YCFlame YCFlame - 6 months ago 9
SQL Question

When will MySQL apply Index Condition Push for a query with order by clause?Why?

I have an article table with a multi column index (author_id, reply_time, id).

explain select * from article where author_id=3658768 and reply_time>'2015-01-01' and id>85669107 order by reply_time
explain select * from article where author_id=3658768 and reply_time>'2015-01-01' and id<85669107 order by reply_time
explain select * from article where author_id=3658768 and reply_time<'2015-01-01' and id>85669107 order by reply_time
explain select * from article where author_id=3658768 and reply_time<'2015-01-01' and id<85669107 order by reply_time


will use Index Condition Push (the "extra" field of explain output is "Using index condition")

However, all queries as follows will not use Index Condition Push (the "extra" field of explain output is "Using where")

explain select * from article where author_id=3658768 and reply_time>'2015-01-01' and id>85669107 order by reply_time desc
explain select * from article where author_id=3658768 and reply_time>'2015-01-01' and id<85669107 order by reply_time desc
explain select * from article where author_id=3658768 and reply_time<'2015-01-01' and id>85669107 order by reply_time desc
explain select * from article where author_id=3658768 and reply_time<'2015-01-01' and id<85669107 order by reply_time desc


Does MySQL use Index Condition Push only for order by asc?

Answer

There is little sense in having an index by author_id, reply_time, id unless you're certain that there will be several ids for the same author_id, reply_time. The index will be no better than being by author_id, reply_time alone.

You'd be better off with two indexes, one by author_id, reply_time and another by author_id, id, and the planner will use the index that is most likely (from collected statistics) that will render the fewer number of rows that have to be sequentially filtered.

Whether the sort is ascending or descending is irrelevant, the planner can make use of an index for sorting, it can use it both for ascending or descending equally well. But sometimes it just can't use it at all, or it's most expensive using it that sorting the rowset.

Comments