Why should I use a small table to drive a large table
1, the definition of the driver table
When a multi-table join query is made, [driver table] is defined as:
1) When a join condition is specified, a table with fewer records rows that meet the query criteria is [driver table]
2) when no join condition is specified, the table with the less number of rows is [driver table] (important!)
Advice: If you don't know who to make the driver table, who joins who, please let MySQL run the self-judgment
Now that the join condition is not specified, the table with fewer rows is [driver table], and you are not quite sure of the complex Nested Loop join you are writing (as shown in the following example), do not specify who left/right join who, please give it to the MySQL optimizer runtime decision.
If you have special confidence in yourself
2. mysql Related query concept:
The algorithm associated with the MySQL table is the Nest loop Join, which uses the result set of the driver table as the loop base data, and then queries the data in the next table with the data in the result set as a filter, and then merges the results.
Example: User table 10,000 data, class table 20 data
SELECT * from the user U left join Class C U.userid=c.userid
This requires the user table to be cycled 10,000 times to query, and if you use the class table to drive the user table will only need to loop 20 times to be able to query out
Cases:
SELECT * FROM class C left join user u C.userid=u.userid
Small result set driven large result set
De.cel concluded in 2012 that, whether you or MySQL, the goal of optimization is to minimize the number of loops in the join nested loop.
This ensures that the large result set (Important) is always driven with a small result set!
Second, the optimization of the Joint table query
Optimize first step: Sort by field of driver table
Left join does not change, why do you want to sort by the fields of the non-driver table? As we said before, "the driver table can be sorted directly, and the non-driver table (the field sort) needs to sort the merged result (temporary table) of the circular query!" Of
Explain
SELECT mb.id ...
From MB left JOIN Mbei on mb.id=mbei.mb_id INNER Joinu on Mb.uid=u.uid
WHERE 1=1
ORDER by Mb.id DESC
Limit 0,10
Also satisfies the business scenario and achieves the following rows minimum:
Optimize the second step: Remove all joins, let MySQL decide, explain the first table is the driver table, the data volume is smaller than the other two tables!
Explain
SELECT
from Mb,mbei,u
WHERE
mb.id=mbei.mb_id
and mb.uid=u.user_id
Order by desc
Limit 0,ten
Immediately, the driver table is the same as the small table Mbei:
ID select_type table type possible_keys key key_len ref rows Extra
1 Simple Mbei all mb_id (NULL) (NULL) 13388 Using filesort
1 Simple MB eq_ref primary,userid PRIMARY 4 mbei.mb_id 1
1 Simple u eq_ref PRIMARY PRIMARY 4 mb.uid 1 Using Index
Iii. Summary
1. Don't trust your luck too much!
2. Don't trust the speed of SQL execution in your development environment!
3, please pick up explain weapon, if you see the following phenomenon, please optimize:
1) A using temporary appears
2) rows too much, or almost all of the records of the table
3) key is (NULL)
4) Possible_keys an excessive (optional) index
Understanding the driver tables in MySQL query, optimizing queries, and driving large tables with small tables