MySQL STRAIGHT_JOIN, mysqlstraight_join

MySQL STRAIGHT_JOIN, mysqlstraight_join
Problem

Recently, I was debugging an SQL statement that took more than 5 seconds to query. This SQL statement uses inner join to sort by time field (order ), an index (index name IDX_published_at) has been created on the time field ). The explain analysis shows that the index on the time field is useless (Using temporary and Using filesort). The problem is obvious, but what is the cause?

SELECT * FROM news n0_ inner join news_translations n1_ ON n0_.id = n1_.translatable_id inner join channels_news c3_ ON n0_.id = c3_.news_id WHERE ((n0_.unpublished_at IS NOT NULL AND (CURRENT_TIMESTAMP >= n0_.published_at AND CURRENT_TIMESTAMP < n0_.unpublished_at)) OR (CURRENT_TIMESTAMP >= n0_.published_at AND n0_.unpublished_at IS NULL))AND (n0_.status = 1 AND n0_.content_type_id = 1) AND n0_.id NOT IN (510466, 510433, 24, 11, 10, 9, 4) AND n0_.home_position_id IS NULL AND n1_.locale = 'zh_CN' AND c3_.channel_id = 1 ORDER BY n0_.published_at DESC LIMIT 5 ;

Optimize pre-SQL statements

+-------+--------+-------------------------------+--------+-----------------------------------------------------------+| table | type   | key                           | rows   | Extra                                                     |+-------+--------+-------------------------------+--------+-----------------------------------------------------------+| c3_   | ref    | IDX_87B9249E72F5A1AA          | 161590 | Using where; Using index; Using temporary; Using filesort || n0_   | eq_ref | PRIMARY                       |      1 | Using where                                               || n1_   | ref    | UNIQ_20FDB3302C2AC5D34180C698 |      1 | Using where                                               |+-------+--------+-------------------------------+--------+-----------------------------------------------------------+

The explain analysis result is deleted.

After a round of optimization, the following SQL statement is obtained.

SELECT * FROM news n0_ STRAIGHT_JOIN news_translations n1_ ON n0_.id = n1_.translatable_id STRAIGHT_JOIN channels_news c3_ ON n0_.id = c3_.news_id WHERE ((n0_.unpublished_at IS NOT NULL AND (CURRENT_TIMESTAMP >= n0_.published_at AND CURRENT_TIMESTAMP < n0_.unpublished_at)) OR (CURRENT_TIMESTAMP >= n0_.published_at AND n0_.unpublished_at IS NULL))AND (n0_.status = 1 AND n0_.content_type_id = 1) AND n0_.id NOT IN (510466, 510433, 24, 11, 10, 9, 4) AND n0_.home_position_id IS NULL AND n1_.locale = 'zh_CN' AND c3_.channel_id = 1 ORDER BY n0_.published_at DESC LIMIT 5 ;

SQL statement after optimization

+-------+--------+-------------------------------+--------+--------------------------+| table | type   | key                           | rows   | Extra                    |+-------+--------+-------------------------------+--------+--------------------------+| n0_   | range  | IDX_published_at              | 255440 | Using where              || n1_   | ref    | UNIQ_20FDB3302C2AC5D34180C698 |      1 | Using where              || c3_   | eq_ref | PRIMARY                       |      1 | Using where; Using index |+-------+--------+-------------------------------+--------+--------------------------+

The explain analysis results are deleted after optimization.

There are four changes before and after optimization: 1. No Using temporary or Using filesort; 2. the table query is changed; 3. The query scan rows is increased; 4. the query time is reduced from 5s to 0.02 s.

Cause Analysis

The four-point changes that occur before and after the optimization significantly improve the performance. We need to start with the related connection processing of mysql.

See High-Performance MySQL

1) the SQL statement before optimization uses channels_news as the first join table and finds 161590 records. 2) the optimized SQL statement uses the news table as the first join table and finds 255440 records, more than more than the first SQL statement. Therefore, the Association Sequence of the SQL statements before optimization is selected by the MySQL optimizer, which allows queries to perform smaller nested loops and backtracking operations. MySQL selects the appropriate Association sequence to minimize the query execution cost. Redefining the association sequence is an important part of the optimizer. However, sometimes the optimizer does not provide the optimal Association sequence. In this case, you can use the STRAIGHT_JOIN keyword to overwrite the query, so that the optimizer can execute the query in the optimal order of association.

According to the explain analysis results after optimization, news is the driving table and the results are sorted by the published_at field of the news table. Therefore, indexes are used to avoid Using temporary and Using filesort. Naturally, the query time is also reduced. As mentioned above, the mysql optimizer selects the order of connection through a rough small table driving large table. The first SQL statement scans 161590 rows, and the second SQL statement scans 255440 rows, the number of lines scanned by the optimized SQL statement increases.

Conclusion

Closing statement: the reason why the SQL statement query takes 5 seconds is that the SQL statement order by field is not in the mysql optimizer and is selected on the driver table, therefore, the index on the sorting field of the associated query is not used. Therefore, you can use STRAIGHT_JOIN to forcibly specify the order of the associated query tables for optimization. However, sometimes we manually specify that the sequence is not necessarily more accurate than the mysql Optimization engine, so we will think twice before using STRAIGHT_JOIN.

Link: http://www.hcoding.com /? P = 211

Original article. For more information, see JC & hcoding.com.

Shu Mao

Luyou

When I was young, I knew the world was busy, and the Central Plains looked forward to the mountains.

Building boat night snow guazhoudu, tiema autumn wind.

The Great Wall has been inserted in the air, and the mirror has been spotted first.

The real name of an artist is a thousand times.