MySQL Learning: Combined query Efficiency analysis

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 statement:

SELECT ID, name, action

from user as u left join user_action A on u.id = a.user_id

Results:

Id	Name	Action
1	Libk	Jump
1	Libk	Kick
1	Libk	Jump
2	Zyfon	Run
3	Daodao	Null

Analysis:

Note that there is also a user_id=4, Action=swim record in the user_action, but not in the result, and the user table Id=3,name=daodao users have no corresponding records in User_action. However, it appears in the result set because it is now a left join, and all work is based on the left table.

Conclusion:

The left join works by reading one from the left-hand table and selecting all the right table records (n bars) to match on to form N records (including duplicate rows). If there is no table on the right that matches the on condition, the connected fields are null. Then read on to the next one.

Extended:

You can add a where condition or a limit keyword after the SQL statement above to make a range limit on the result set like a normal SQL statement.

We can use the right table without 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 to be judged must be declared not NULL.

SQL statement:

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

Attention:

1, the column value ewingnull should be null and cannot use = NULL

2, here the a.user_id column must be declared as NOT null

Results:

Id	Name	Action
3	Daodao	Null

Skills:

1, on a.c1 = B.C1 equivalent to using (C1)

2, INNER Join and, (English comma) is semantically equivalent

3. When recovering/retrieving information from a table, you can provide hints as to which index MySQL should use. By specifying user index (key_list), you can tell MySQL to use only one index to find rows in the table. Another syntax, ignore index (key_list), tells MySQL not to use specific indexes. These hints are useful if explain shows that MySQL is using an error index from the index list.

It is also possible to use force index, which is close to usage index (key_list), but adds a role in which a table scan is assumed to be expensive. In other words, table scans are used only if a given index cannot be used to find rows in a table.

Note: Using use index, IGNORE Index, and force index only affects which indexes are used when MySQL decides how to find rows in a table and decide how to make a union. These statements do not affect whether an index is used, when an order by or group by IS exploded.

Example:

SELECT * FROM table1, table2 where table1.id=table2.id;

SELECT * FROM table1 left JOIN table2 on table1.id=table2.id;

SELECT * FROM table1 left JOIN table2 USING (ID);

SELECT * FROM table1 left JOIN table2 on table1.id=table2.id

Left JOIN Table3 on table2.id=table3.id

SELECT * FROM table1 use INDEX (Key1, Key2) WHERE key1=1 and key2=2 and key3=3

SELECT * FROM table1 IGNORE INDEX (Key3) WHERE key1=1 and key2=2 and key3=3

How MySQL optimizes left join and right join

In MySQL, A left JOIN B join_condition is executed as follows:

• Set Table B based on all tables A and a dependent on tables
• Set Table A based on all tables used in the left join condition (except B)
• The LEFT join condition is used to determine how to search for rows from table B (in other words, without using 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 relies. If there is a circular dependency, MySQL prompts for an error.
• Make all standard where optimizations
• If a row in a matches a WHERE clause, but no row in B matches the on condition, then another B row is generated, where all the columns are set to NULL.
• If you use a LEFT join to find rows that do not exist in some tables, and the following tests are performed, the where part of the col_name is NULL, where the col_name is a column declared not NULL, and MySQL finds a matching left Stop after one line of the join condition (for a specific keyword combination) to search for additional rows.

The execution of the right join is similar to a left join, but the role of the table is reversed.

Joins the optimizer in the order in which the tables should be joined. Left JOIN and Straight_join forced table read Order can help the Join optimizer work faster because fewer table exchanges are checked. Please note this description. If you perform the following type of query, MySQL performs a B full scan because the left join forces it to read before D:

SELECT * from A, B-left join C in (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" (C.key=a.key) left join D on (D.key=a.key)

WHERE B.key=d.key

MySQL can perform the following left join optimization, and if for the resulting null row the Where condition is always false, 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 a query to a normal join:

SELECT * from T1, T2 where t2.column2=5 and t1.column1=t2.column1;

This can be faster because MySQL can use table t2 before table T1 If you can make the query better. To enforce the use of table order, use the Straight_join