MySQL Performance – Should Order By Column Have an Index?

indexMySQLorder-byperformance

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?

Best Answer

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.

Related Solutions

T-sql – CASE ORDER BY with multiple columns, and different sort options

Break it out a little more:

ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC,
         CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC,
         tp.lastname ASC,
         tp.firstname ASC

You only need the sort order to change on the first field, so don't enclose the others in the CASE.

It should be noted that we don't include an ELSE for each CASE, which means any other value will return NULL and be discarded from the ORDER BY.

Sql-server – How does order of index affect performance in SQL Server

In this article someone looked at the performance of an index and the ordering of the data in the index.

Summary

As we have shown creating an index in ascending or descending order does not make a big difference when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the other column in descending order the way the index is created does make a big difference.

Best Answer

Related Solutions

T-sql – CASE ORDER BY with multiple columns, and different sort options

Sql-server – How does order of index affect performance in SQL Server

Related Question