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?


2 Answers 2


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 (SELECT * FROM foo ORDER BY bar ASC, pants DESC). Sharing your query and CREATE TABLE statement would help us answer your question more specifically.

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:

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:


You mix ASC and DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;


Also, the DESC keyword is meaningless in CREATE INDEX

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

  • 4
    "MySQL cannot use an index for mixed ASC,DESC". Reference for this? Jan 21, 2012 at 9:26
  • CREATE INDEX idx_foo ON foo(bar ASC, pants DESC); should work. Jan 21, 2012 at 10:38
  • My source is the mysql documentation linked in my answer. Search for "mix." Jan 21, 2012 at 12:43
  • 4
    MySQL parses asc & Desc in CREATE INDEX, but ignores them. Indexes are always stored in ascending order. dev.mysql.com/doc/refman/5.5/en/create-index.html Jan 21, 2012 at 12:54
  • 5
    New with MySQL 8.0: Now it can use a mixture of ASC and DESC if the INDEX has the same mixture.
    – Rick James
    Sep 18, 2019 at 6:19

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.

  • OP is about MySQL not PostgreSQL
    – Hybris95
    Nov 30, 2021 at 8:48
  • 1
    @Hybris95 I don't see how that is relevant. If you look at the actual content of the question asked, it does not specifically reference MySQL; the only reference to MySQL is a tag. And since someone might arrive here based on the wording of the question, my answer remains appropriate.
    – douglasr
    Dec 2, 2021 at 20:39
  • 1
    I was looking exactly this for Postgres, and Google sent me here... Thank you for great answer @douglasr
    – zauzaj
    Aug 4, 2022 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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