1

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

Ahn
  • 181
  • 3
  • 5
  • 13

2 Answers2

1

Look into clustered indexes, they can be used to improve performance in some cases with large data sets, apparently that was what the SQL style DB's used back when computing power was very limited.

Source: I'm working with a senior developer who mentioned they would use an index to avoid having to select the actual values in the database because of throughput restrictions.

According to the documentation I've checked they are just 'primary keys' but they're not the same as what they once were, might require digging to get any obscure performance benefit out of it.

Additional information can be found here: https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

See headline "Influencing ORDER BY Optimization" in the referenced article.

Gordo
  • 11
  • 1
0

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.

Alex
  • 7,939
  • 6
  • 38
  • 52
  • 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