Understanding the driver tables in MySQL query, optimizing queries, and driving large tables with small tables

Source: Internet
Author: User
Tags joins mysql query

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.