One MySQL order by optimization case

1. Desc SELECT ActionId, UserID, CreateUser, ActionType, ActionName, Actioncomment, CreateDate, Pointsrulesid,reason, Objectid,byuserid,byusername,subjectid from Wikiuseractionlog Where createdate> ' 0001-01-01 00:00:00 ' and ActionType in (10,9,19,20) ORDER by createdate DESC limit 93060, 20;

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------------------+------+------------------------+------+---------+------+-------+------- ----------------------+

| 1 | Simple | Wikiuseractionlog | All | Idx_date_type,idx_date | NULL | NULL | NULL | 91859 | Using where; Using Filesort

Look at the execution plan, not the index, although I built a composite index Idx_date_type (createdate,actiontype) and a column index idx_date (createdate), to make the above statement walk the index as long as there are the following points:

1, need to change the date ' 0001-01-01-00:00:00 ' to 2014-01-01 00:00:00 (2013 or less, do not know why)

2, you need to make createdate and ActionType composite Index! In general, if the order by field is to go through an index optimization, you will need to make a federated index of the field under where Condition and order by!

3,limit limit 93060,20 Before this limit will affect the number of scanned rows! 93060 rows of data must be scanned regardless of index! Actually, otherwise! As long as the index is used, it just takes the data !

Mysql> desc SELECT ActionId, UserID, CreateUser, ActionType, ActionName, Actioncomment, CreateDate, Pointsrulesid, Reason,objectid,byuserid,byusername,subjectid from Wikiuseractionlog ORDER by CreateDate;

+----+-------------+-------------------+------+---------------+------+---------+------+-------+---------------- +

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------------------+------+---------------+------+---------+------+-------+---------------- +

| 1 | Simple | Wikiuseractionlog | All | NULL | NULL | NULL | NULL | 95220 | Using Filesort |

+----+-------------+-------------------+------+---------------+------+---------+------+-------+---------------- +

1 row in Set (0.00 sec)

Mysql> desc SELECT ActionId, UserID, CreateUser, ActionType, ActionName, Actioncomment, CreateDate, Pointsrulesid, Reason,objectid,byuserid,byusername,subjectid from Wikiuseractionlog Where createdate> ' 2014-01-01 00:00:00 ' ORDER by CreateDate DESC limit 93060, 20;

+----+-------------+-------------------+-------+------------------------+---------------+---------+------+----- -+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------------------+-------+------------------------+---------------+---------+------+----- -+-------------+

| 1 | Simple | Wikiuseractionlog | Range | Idx_date_type,idx_date | Idx_date_type | 8 | NULL | 3737 | Using where

In this case, although there is a separate index idx_date (createdate), the discovery is still not indexed, because the order by field must also appear in the Where condition, the reason is that the sort operation is in the final, the first time to extract data, If the field in the Where condition has an index, when it is extracted, it has already done a sort operation, then the order by is not filesort, otherwise, in the last extracted data in order BY, Nature will do filesort!!

Mysql> desc SELECT ActionId, UserID, CreateUser, ActionType, ActionName, Actioncomment, Pointsrulesid,reason, Objectid,byuserid,byusername,subjectid from Wikiuseractionlog Where ActionType in (10,9,19,20) ORDER by CreateDate;

+----+-------------+-------------------+------+---------------+------+---------+------+-------+---------------- -------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------------------+------+---------------+------+---------+------+-------+---------------- -------------+

| 1 | Simple | Wikiuseractionlog | All | Idx_type_date | NULL | NULL | NULL | 90747 | Using where; Using Filesort |

+----+-------------+-------------------+------+---------------+------+---------+------+-------+---------------- -------------+

1 row in Set (0.00 sec)

The above example does not use the index! The following is possible! Change ActionType in (10,9,19,20) to or or not!

Mysql> desc SELECT ActionId, UserID, CreateUser, ActionType, ActionName, Actioncomment, Pointsrulesid,reason, Objectid,byuserid,byusername,subjectid from wikiuseractionlog Where actiontype = ' Ten ' ORDER by CreateDate;

+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+------ -------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+------ -------+

| 1 | Simple | Wikiuseractionlog | Ref | Idx_type_date | Idx_type_date | 3 | Const | 45373 | Using where |

+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+------ -------+

1 row in Set (0.00 sec)

This article is from the "husband's minister" blog, please be sure to keep this source http://fucheng.blog.51cto.com/2404495/1441534

One MySQL order by optimization case