Efficiency Analysis of MySQL left join queries

User table: <br/> ID | Name <br/> --- <br/> 1 | libk <br/> 2 | zyfon <br/> 3 | daodao <br/> user_action table: <br/> user_id | action <br/> ----- <br/> 1 | jump <br/> 1 | kick <br/> 1 | jump <br/> 2 | run <br/> 4 | swim <br/> SQL: <br/> select ID, name, action from user as U <br/> left join user_action A on u. id =. user_id <br/> result: <br/> ID | Name | action <br/> ----------- <br/> 1 | libk | jump ① <br/> 1 | libk | kick ② <B R/> 1 | libk | jump ③ <br/> 2 | zyfon | run ④ <br/> 3 | daodao | null ⑤ <br/> analysis: <br/> note that user_action has a user_id = 4, Action = swim record, but it does not appear in the result. <br/> in the User table, id = 3, name = daodao users do not have corresponding records in user_action, but they appear in the result set. <br/> because left join is used, left prevails for all work. <br/> results 1, 2, 3, and 4 are both records in both the left and right tables, and 5 is only records in the left table, records not in the right table </P> <p> conclusion: <br/> we can imagine that left join works in this way. <br/> Read a 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 ), <br/> if there is no on condition on the right side In the matched table, all connected fields are null. <br/> then read the next one. <Br/> extended: <br/> we can use the rule that the right table shows null if there is no on match to find all records in the left table but not in the right table, note that the column used for judgment must be declared as not null. <Br/> example: <br/> SQL: <br/> select ID, name, action from user as U <br/> left join user_action A on u. id =. user_id <br/> where. user_id is null <br/> (Note: 1. if the column value is null, is null should be used instead of = NULL <br/> 2. here. the user_id column must be declared as not null) <br/> result: <br/> ID | Name | action <br/> --------- <br/> 3 | daodao | null <br/> ------------------------- <br/> tips: <br/> 1. on. c1 = B. c1 is equivalent to using (C1) <br/> 2. inner join and, (comma) It is semantically equivalent <br/> 3. When MySQL retrieves information from a table, you can prompt which index it chooses. <Br/> this feature is useful if the explain command shows that MySQL has used an index with a possible index list error. <Br/> by specifying the use index (key_list), you can tell MySQL to use the most appropriate index possible for searching record rows in the table. <Br/> the optional syntax ignore index (key_list) can be used to tell MySQL that a specific index is not used. <Br/> 4. some examples: <br/> mysql> select * From Table1, Table2 where table1.id = table2.id; <br/> mysql> select * From Table1 left join Table2 on table1.id = table2.id; <br/> mysql> select * From Table1 left join Table2 using (ID ); <br/> mysql> select * From Table1 left join Table2 on table1.id = table2.id <br/>-> left join table3 on table2.id = table3.id; <br/> mysql> select * From Table1 use index (key1, key2) <br/>-> Where key1 = 1 and key2 = 2 and key3 = 3; <br/> mysql> select * From Table1 ignore index (key3) <br/>-> where key1 = 1 and key2 = 2 and key3 = 3; <br/> 7.2.9. how does MySQL optimize left join and right join? <br/> in MySQL, the execution process of a left join B join_condition is as follows: <br/> · set table B based on all tables on which table A and table a depend. <Br/> · set Table A according to all tables (except B) used in the left join condition. <Br/> 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 ). <Br/> · 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. <Br/> · perform all standard where optimizations. <Br/> · 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, with all columns set to null. <Br/> · if left join is used to locate rows that do not exist in some tables, and the following test is performed: The col_name is null in the where section, here, col_name is a column declared as not null. MySQL finds a row matching the left join condition and stops searching for other rows (for a specific keyword combination. <Br/> the execution of right join is similar to that of left join, but the role of the table is the opposite. <Br/> 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: <br/> select * <br/> from, B left join C on (C. key =. key) left join D on (D. key =. key) <br/> where B. key = D. key; <br/> in this case, the reverse order of A is used, and B is listed in the from clause: <br/> select * <br/> from B, A left join C on (C. key =. key) left join D on (D. key =. key) <br/> where B. key = D. key; <br/> 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. <Br/> for example, if t2.column1 is null in the following query, the where clause will be false: <br/> select * from T1 left join T2 on (column1) where t2.column2 = 5; <br/> therefore, you can safely convert a query to a normal join: <br/> select * from T1, T2 where t2.column2 = 5 and t1.column1 = t2.column1; <br/> This can be faster, because MySQL can use table T2. To force table order, use straight_join.