One MySQL order by optimization case

Source: Internet
Author: User

    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

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.