Analysis of the efficiency of MYSQL left join joint query

User table:

ID | Name
———
1 | Libk
2 | Zyfon
3 | Daodao

User_action table:

user_id | Action
—————
1 | Jump
1 | Kick
1 | Jump
2 | Run
4 | Swim

Sql:
Select ID, name, action from user as U
Left join user_action A on u.id = a.user_id

Result
ID | name | Action
——————————–
1 | LIBK | Jump①
1 | LIBK | Kick②
1 | LIBK | Jump③
2 | Zyfon | Run④
3 | Daodao | Null⑤

Analysis:
Notice that there is also a user_id=4, Action=swim record in the user_action, but it did not appear in the results,
Id=3 in the user table, Name=daodao users do not have a corresponding record in the user_action, but appear in the result set
Because now is the left join, all the work is left to prevail.
Results 1,2,3,4 are both in the left table and in the right table record, 5 is only in the left table, not the right table record

Conclusion:
We can imagine that the left join works like this.
Read one from the left table, select all the right table records (n) to match on, and Form N records (including duplicate rows, such as result 1 and result 3),
If there are no tables on the right that match the on condition, the joined fields are null.
Then read on to the next article.

Extended:
We can use the right table without an on match to show the law of NULL, to find all the records in the left table, not the right table, note that the column used to judge must be declared as NOT null.
Such as:
Sql:
Select ID, name, action from user as U
Left join user_action A on u.id = a.user_id
where a.user_id is NULL
(Note: 1. column value NULL should be null instead of =null
2. Here the a.user_id column must be declared as not NULL)
Result
ID | name | Action
————————–
3 | Daodao | Null

——————————————————————————–

Tips:
1. On a.c1 = B.C1 equivalent to using (C1)
2. INNER JOIN and, (comma) are semantically equivalent
3. When MySQL retrieves information from a table, you can indicate which index it chooses.
This feature is useful if EXPLAIN shows that MySQL uses the wrong index in the list of possible indexes.
By specifying use index (key_list), you can tell MySQL to find a record row in the table using the most appropriate index in the possible index.
Optional two syntax IGNORE index (key_list) can be used to tell MySQL not to use a specific index.
4. Some examples:
Mysql> SELECT * from Table1,table2 WHERE table1.id=table2.id;
Mysql> SELECT * FROM table1 left JOIN table2 on table1.id=table2.id;
Mysql> SELECT * FROM table1 left JOIN table2 USING (ID);
Mysql> SELECT * FROM table1 left JOIN table2 on table1.id=table2.id
-left JOIN table3 on table2.id=table3.id;
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;

7.2.9. How MySQL optimizes left join and right join
In MySQL, A left JOIN B join_condition executes the following procedure:

· Table B is set up for all tables that depend on tables A and a.

· Table A is set according to all tables (except B) used in the left join condition.

· The LEFT join condition is used to determine how rows are searched from Table B. (In other words, do not use any of the conditions in the WHERE clause).

· All standard joins can be optimized, except for tables that are read from all tables on which it depends. If there is a circular dependency, the MySQL prompt appears an error.

· Perform all standard where optimizations.

· If there is a row in a that matches the WHERE clause, but no row in B matches the on condition, another B row is generated, and all the columns are set to NULL.

· If you use the left join to find rows that do not exist in some tables, and test the following: Col_name is null in the Where section, where Col_name is a column declared as NOT NULL, and MySQL finds a match A row after the join condition stops (for a specific keyword combination) to search for additional rows.

The right join executes like a LEFT join, except that the table's role is reversed.

The Join Optimizer calculates the order in which tables should be joined. The table read order enforced by the left join and Straight_join can help the Join optimizer work faster because there are fewer table exchanges to check. Note that this means that if you perform the following type of query, MySQL performs a full scan of B, because the left join forces it to read before D:

SELECT *
From a B left join C on (C.key=a.key) left join D on (D.key=a.key)
WHERE B.key=d.key;
In this case the fix is in the reverse order of a, and B is listed in the FROM clause:

SELECT *
From B,a left join C on (C.key=a.key) left join D on (D.key=a.key)
WHERE B.key=d.key;
MySQL can perform the following left join optimizations: If the Where condition is always false for the resulting null row, the left join becomes a normal join.

For example, in the following query, if T2.COLUMN1 is the Null,where clause, it will be false:

SELECT * from T1 left JOIN T2 on (column1) WHERE t2.column2=5;
Therefore, you can safely convert the 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 the table t1. To enforce the use of table order, use Straight_join.

Referenced from: http://www.phpchina.com/archives/view-33200-1.html