I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How performance will affect by making or not making index to that column?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Yes, MySQL can use an index on the columns in the ORDER BY (under certain conditions). However, MySQL cannot use an index for mixed ASC,DESC order by (
For hints on how to optimize ORDER BY clauses: http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html
Edit 2012-01-21 8:53AM
There were questions about the source of my statement about using an index with mixed ASC/DESC in the ORDER BY. From the ORDER BY Optimization documentation:
Also, the DESC keyword is meaningless in CREATE INDEX
For anyone wondering about the answer as it relates to PostgreSQL, the answer is yes an index will be used. As per the PostgreSQL documentation the query planner "will consider satisfying an ORDER BY specification either by scanning an available index that matches the specification, or by scanning the table in physical order and doing an explicit sort." This means that if there is an index that the planner can use it will do so, falling back to doing a sort after retrieving the matching rows.