Creating indexes in MySQL improves the efficiency of Multi-table queries

The purpose of this article is to discuss the basic principles of the use index for join statement. We know that when MySQL uses the jion statement to associate a multi-Table query, creating an index for the table can greatly improve the query efficiency. The following is a simple example to understand the principles.

Simplify the model. Assume that there are three tables: tbla, TBLB, and tblc. Each table contains three columns: col1, col2, and col3. other attributes of the table are not considered.

Without creating an index, we use the following statement to associate three tables:

1    SELECT2       *3    FROM4       tblA,5       tblB,6       tblC7    WHERE8           tblA.col1 = tblB.col19       AND tblA.col2 = tblC.col1;

Use the explain command to view the statement processing status:

   +-------+------+---------------+------+---------+------+------+-------------+   | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |   +-------+------+---------------+------+---------+------+------+-------------+   | tblA  | ALL  | NULL          | NULL |    NULL | NULL | 1000 |             |   | tblB  | ALL  | NULL          | NULL |    NULL | NULL | 1000 | Using where |   | tblC  | ALL  | NULL          | NULL |    NULL | NULL | 1000 | Using where |   +-------+------+---------------+------+---------+------+------+-------------+

For details about the meanings of parameters in the explain command, see the blog post of Ali.

[Http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html]

 

Query Mechanism

For the command query mechanism, refer to the description in MySQL Manual (7.2.1) below:

The tables are listed in the output in the order that MySQL wocould read them while processing the query. mySQL resolves all joins using a single-sweep multi-join method. this means that MySQL reads a row from the first table, then finds a matching row in the second table, then in the third table, and so on. when all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. the next row is read from this table and the process continues with the next table.

As mentioned above, MySQL reads data sequentially in the order of tbla, TBLB, and tblc. From the information structure output in the explain statement, the data queried in the previous table is used to find the corresponding content of the current table. The value of tbla is used to find the values that meet the conditions in TBLB. The value of TBLB is used to find the values that meet the conditions in tblc. When a query is complete (that is, the values of all three tables are searched once), MySQL does not return to the next data in tbla to start again, but continues to return to the data in TBLB, check whether the values of other rows in TBLB match tbla. If yes, continue to tblc and repeat the process. The key principle of this process is to use the data queried from the previous table to find the corresponding content of the current table.

 

After learning about the principle that MySQL uses the data queried in the previous table to find the content of the current table when performing a multi-Table query, the purpose of creating an index is to tell MySQL how to directly find the corresponding data of the next table and how to join a table according to the data sequence required by MySQL.

In the preceding example, tbla and TBLB are joined by the condition "tbla. col1 = TBLB. col1. First, we get tbla. col1. Next MySQL needs a value from TBLB. col1. So we create index TBLB. col1. the query command before creating index and then explaining again is as follows:

   +-------+------+---------------+----------+---------+-----------+------+-------------+   | table | type | possible_keys | key      | key_len | ref       | rows | Extra       |   +-------+------+---------------+----------+---------+-----------+------+-------------+   | tblA  | ALL  | NULL          | NULL     |    NULL | NULL      | 1000 |             |   | tblB  | ref  | ndx_col1      | ndx_col1 |       5 | tblA.col1 |    1 | Using where |   | tblC  | ALL  | NULL          | NULL     |    NULL | NULL      | 1000 | Using where |   +-------+------+---------------+----------+---------+-----------+------+-------------+

From the results, we can see that MySQL uses the key 'ndx _ col1' to associate TBLB to tbla. That is to say, when MySQL finds all rows of data in TBLB, it directly uses the tbla corresponding to key 'ndx _ col1. col1 to find the corresponding row, instead of performing full table scan as before.

 

Example

An example is provided to illustrate the usage.

The using parameter must be the same as that of each table and the values in each table are not repeated to ensure that the index is unique.

In join (primary), the primary parameter is the index name,

In the table attributes, You must select the PK attribute as the index parameter, that is, the primary key.

If telnum is selected as the primary key, the default null value must be deleted. primary_key cannot contain null values.

 

After an index value is created for each table, the explain output is:

For MySQL, no matter how complicated the query is, you only need to associate the content in the two tables in the order displayed in the explain statement. Index is created to allow MySQL to quickly find the corresponding row content of the next table using the searched content.

 

 

Reference: [http://hackmysql.com/case4] [How to index for joins with MySQL]