Mysql join Optimization

Mysql join optimization 1. multi-Table connection type 1. cartesian products (cross join) can be considered as cross join in MySQL, or CROSS is omitted, or ',' such: SELECT * FROM table1 cross join table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1, table2 returns the product of the two joined data tables, so when there is a WHERE, ON or USING conditions are generally not recommended, because when there are too many data tables, it will be very slow. Generally, LEFT [OUTER] JOIN or RIGHT [OUTER] JOIN 2 is used. in MySQL, inner join is called an equijoin, that is, the equijoin conditions must be specified. In MySQL, CROSS and inner join are grouped together. Join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] 3. mySQL outer join is divided into left Outer Join and right join. In addition to returning results that meet the connection conditions, the left table (left join) or right table (right join) is also returned) results that do not meet the connection conditions, corresponding to the use of NULL. Example: user table: id | name --- 1 | libk2 | zyfon3 | daodao user_action table: user_id | action ----- 1 | jump1 | kick1 | jump2 | run4 | swim SQL: select id, name, action from user as u left join user_action a on u. id =. user_id result: id | name | action ----------- 1 | libk | jump ① 1 | libk | kick ② 1 | libk | jump ③ 2 | zyfon | run ④ 3 | daodao | null ⑤ analysis: note that user_action has a record with user_id = 4, action = swim, but it does not appear in the result, while The id = 3, name = daodao user in the er table does not have a corresponding record in user_action, but it appears in the result set. Because it is left join, all the work is subject to left. result 1, 2, 3, and 4 are records in both the left table and the right table. 5 is a record in the left table and not in the right table. How can this problem be solved? Read one record from the left table, select All records (n records) in the right table that match on to Form n records (including repeated rows, such as result 1 and result 3 ), if there is no table matching the on condition on the right side, all connected fields are null. then read the next one. Extended: We can use the right table to show the null rule without on matching to find all records in the left table, not in the right table, note that the column used for judgment must be declared as not null. For example, SQL: select id, name, action from user as u left join user_action a on u. id =. user_id where. user_id is NULL (Note: 1. if the column value is null, it should be "is null" instead of "= NULL. here. the user_id column must be declared as not null .) result: id | name | action --------- 3 | daodao | NULL --------------------------- of the preceding SQL statement:. LEFT [OUTER] JOIN: In addition to returning results that meet the connection conditions, you must also display data columns that do not meet the connection conditions in the LEFT table, the corresponding NULL clause corresponds to SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column B. RIGHT [OUTER] JOIN: the difference between RIGHT and left join is that in addition to displaying results that meet the connection conditions, you must also display data columns that do not meet the connection conditions in the RIGHT table, the corresponding use of NULL corresponds to SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column Tips: 1. on. c1 = B. c1 is equivalent to using (c1) 2. inner join and (comma) are semantically equivalent to 3. when MySQL retrieves information from a table, you can prompt which index it chooses. This feature is useful if the EXPLAIN command shows that MySQL uses an index that may be incorrect in the index list. By specifying the use index (key_list), you can tell MySQL to USE the most appropriate INDEX to find record rows in the table. The optional syntax ignore index (key_list) can be used to tell MySQL not to use a specific INDEX. For example, mysql> SELECT * FROM table1 use index (key1, key2)-> WHERE key1 = 1 AND key2 = 2 AND key3 = 3; mysql> SELECT * FROM table1 ignore index (key3)-> WHERE key1 = 1 AND key2 = 2 AND key3 = 3; 2. add the WHERE, ON, USING 1 display condition to the constraints of table join. WHERE clause mysql> SELECT * FROM table1, table2 WHERE table1.id = table2.id; 2. ON mysql> SELECT * FROM table1 left join table2 ON table1.id = table2.id; SELECT * FROM table1 left join table2 O N table1.id = table2.id left join table3 ON table2.id = table3.id; 3. USING clause. If the two columns of the JOIN two tables have the same names, you can use USING for example: select from left join using () to JOIN more than two tables. Example: mysql> SELECT artists. artist, cds. title, genres. genre FROM cds left join genres N cds. genreID = genres. genreID left join artists ON cds. artistID = artists. artistID; or mysql> SELECT artists. artist, cds. title, genres. genre FROM cds left join g Enres ON cds. genreID = genres. genreID left join artists-> ON cds. artistID = artists. artistID WHERE (genres. genre = 'pop'); ------------------------------------------ note that when multi-table queries are involved in MySQL, You need to determine which connection method is more efficient Based on the query conditions. 1. cross join (Cartesian Product) or inner join [INNER | CROSS] JOIN 2. left outer join left [OUTER] JOIN or right outer join right [OUTER] JOIN Note: Specify the connection conditions WHERE, ON, USING. 3. how does MySQL optimize left join and right join? in MySQL, the execution process of a left join B join_condition is as follows: 1) · set table B according to all tables on which table A and table A depend. 2). Set Table A according to all tables used in the left join condition (except B. 3) The left join condition is used to determine how to search rows from Table B. (In other words, do not use any conditions in the WHERE clause ). 4) · all standard joins can be optimized, except for tables read from all the tables it depends on. If a circular dependency occurs, MySQL prompts an error. 5) perform all standard WHERE optimizations. 6) If A has A row that matches the WHERE clause, but B does not have A row that matches the ON condition, another B row is generated, and all columns are set to NULL. 7). If left join is used to locate rows that do NOT exist in some tables and perform the following test: col_name is null in the WHERE section, WHERE col_name IS a column declared as not null, mySQL finds a row that matches the left join condition and stops searching for other rows (for a specific keyword combination. The execution of right join is similar to that of left join, but the role of the table is the opposite. The order in which the join optimizer calculates the table to be joined. The forced read sequence of left join and STRAIGHT_JOIN can help the JOIN optimizer to work faster, because fewer table exchanges are checked. Note that if the following type of query is executed, MySQL performs full scan B, because left join forces it to read before d: SELECT * FROM a, B LEFT JOIN c ON (c. key =. key) left join d ON (d. key =. key) WHERE B. key = d. key; in this case, the reverse order of a is used, and B is listed in the FROM clause: SELECT * FROM B, a LEFT JOIN c ON (c. key =. key) left join d ON (d. key =. key) WHERE B. key = d. key; MySQL can perform the following left join optimization: If the NULL row is generated, the WHERE condition is always false, and the left join is changed to a normal JOIN. For example, if t2.column1 is NULL in the following query, the WHERE clause will be false: SELECT * FROM t1 left join t2 ON (column1) WHERE t2.column2 = 5; therefore, you can safely convert a query to a normal join: SELECT * FROM t1, t2 WHERE t2.column2 = 5 AND t1.column1 = t2.column1; this can be faster, because if you can make the query better, mySQL can use table t2 before table t1. To force table order, use STRAIGHT_JOIN.