MySQL go wrong index causes driver table to be selected incorrectly

Original sql:

Select COUNT (*) from Mpay_order mpayorder inner join mrecharge_order_info orderinfo on mpayorder.order_num = Orderinfo.ord Er_num INNER JOIN Mpay_trade mpaytrade on mpayorder.order_num = Mpaytrade.order_num where TRUE and left (mpaytrade.trade_nu m,2) = ' Ten ' and Timestampdiff (Second,mpayorder.create_time, ' 2014-12-19 00:00:00 ') <= 0 and Timestampdiff (SECOND, Mpayorder.create_time, ' 2014-12-19 12:40:32 ') >= 0;

Execution Plan:

+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+-- ------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+-- ------+-------------+
| 1 | Simple |OrderInfo| Index | Order_num_idx | Order_num_idx | 93 | NULL |184192| Using Index |
| 1 | Simple | Mpaytrade | Ref | Order_num_idx | Order_num_idx | 93 | Mpay.orderinfo.order_num | 1 | Using where |
| 1 | Simple | Mpayorder | Ref | Order_num_idx | Order_num_idx | 93 | Mpay.mpayTrade.order_num | 1 | Using where |
+----+-------------+-----------+-------+---------------+---------------+---------+--------------------------+-- ------+-------------+

Execution Time: 1 row in Set (2.69 sec)

===> recommended optimization for, change into the following:

Select COUNT (*) from Mpay_order mpayorder inner join mrecharge_order_info orderinfo on mpayorder.order_num = Orde Rinfo.order_num INNER JOIN Mpay_trade mpaytrade on mpayorder.order_num = Mpaytrade.order_num where TRUE and left (Mpaytrade . trade_num,2) = ' Ten ' and mpayorder.create_time>= ' 2014-12-19 00:00:00 ' and mpayorder.create_time<= ' 2014-12-19 12:40:32 ';

Execution Plan:

+----+-------------+-----------+-------+-------------------------------+-----------------+---------+----------- ---------------+------+--------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-----------+-------+-------------------------------+-----------------+---------+----------- ---------------+------+--------------------------+
| 1 | Simple |Mpayorder| Range | Order_num_idx,create_time_idx | Create_time_idx | 9 | NULL |1484| Using where |
| 1 | Simple | OrderInfo | Ref | Order_num_idx | Order_num_idx | 93 | Mpay.mpayOrder.order_num | 1 | Using where; Using Index |
| 1 | Simple | Mpaytrade | Ref | Order_num_idx | Order_num_idx | 93 | Mpay.mpayOrder.order_num | 1 | Using where |
+----+-------------+-----------+-------+-------------------------------+-----------------+---------+----------- ---------------+------+--------------------------+

Execution Time: 1 row in Set (0.03 sec)

Optimization Ideas : Remove function Timestampdiff, building an index ALTER TABLE Mpay_order Add index ' Create_time_idx  ' (create_time);

through the Mpay_order Index, the optimizer chooses a small Driver Table Mpayorder 1484 , reducing the cost of nest loops.

The index has been added, and SQL needs to be rewritten.

This article is from the My DBA life blog, so be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1596179

MySQL go wrong index causes driver table to be selected incorrectly