The picture above you can see saleout table with scroll_date and create_time two indexes;




When the order by using create_time descending, found that the speed is more than 4 s
The explain analysis found that the type of the index key create_time search for 2387 line;




But when the order by using the id in descending order, found the speed is 0.05 s
The explain analysis found that the type for ref key for scroll_date search more than 5000 line;



Forced to let it go scroll_date effect came up, and found that its speed is 0.05 s
The explain analysis found that the type for ref key for scroll_date search more than 5000 line; When using the Id and the order by about the same,

Compare the above results can be found:
Index to scroll_date will be a lot faster than create_time,

The question becomes:
1, since scroll_date faster than create_time so much, why don't go mysql scroll_date create_time index and choose?
2, why use the order by the create_time mysql not two indexes are used

CodePudding user response:

The second question is very good answer, you go the mysql statement itself is not the two indexes, go take care of sorting create_time but create_time column index can't realize the scroll_date filtering, mysql only filtered back to the table, on the other hand if go scroll_date then only temporary table sorting after back to the table,

The first question, look at your index scroll_date low degree of differentiation, estimate the mysql think I am to take care of the order and then back to the table to filter may be better,,

-- -- -- -- -- if this statement is only for you, then build a joint index (status, scroll_date, create_time), performance will be very, very good,