Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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. ?

share|improve this question
    
I think By adding a composite will do. –  Ahn Aug 24 '12 at 5:30

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
Alex thanks, But if my query use the fld1 >='XXX'AND fld2>='XXX' AND status=8 what should I do ? If I separately do the fld_date index, will it be useful? –  Ahn Aug 23 '12 at 10:57
    
No it won't be useful then, the only optimization I can think of in that case is to select not * but primary keys only and use a LIMIT clause (I guess a user never needs the whole table). When you get the primary keys you will be able to get everything else in separate query like SELECT * FROM t1 WHERE pk IN (.., .., ..). Queries like SELECT * WHERE some_complex_expression ORDER BY ... effectively kill MySQL sorting buffers, it's always better to sort less data and * can be a lot of data. –  Alex Aug 23 '12 at 11:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.