Mysql – Performance improvement of “ORDER BY” with index

mysql5

Can I improve the "ORDER BY" by adding an index on fld_date.

 SELECT * FROM t1 WHERE fld1='XXX' fld2='XXX' ORDER BY fld_date;

The query uses the index of (fld1,fld2 – combined index) in the where clause. As the ORDER BY does not match the index exactly, If I do add an index with fld_date will it be useful in improving the ORDER BY fld_date performance. ?

Best Answer

You should add an index on (fld1, fld2, fld_date). Please note it will work only if fld1 and fld2 are set to exact match in WHERE clause, non-exact expressions like < or > will turn the index off for ordering.

Related Topic