Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to somehow use that index in reverse, or is it going to have to do a table scan or some other less efficient method of accessing those records and sorting them?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
If the index is covering it's likely it will be used in conjuction with a sort operation in the query plan to reverse the order.
Edit: Following a little more thought!
It will depend on whether this is a trivial query or involves joins. If trivial:
and the index order is Y ASC, a reverse scan of the index leaf level should avoid a sort.
it should depend on the sort order of MyOtherTable.y. If it's ASC as per MyTable.y then the two indexes would be read in index order and a sort applied after the join. If it's desc, in theory a reverse order index scan could be used for the join and an additional sort wouldn't be required to satisfy your order by clause.
Edit2: Couldn't recall if this would show up in the execution plan in SQL Server. The icon doesn't indicate this is a reverse scan, nor does the tooltip on hover. Properties however shows 'Scan Direction - Backward' or checking the plan XML reveals
It can use the index but may need an extra sort to reverse it.
Ive noticed that later versions of SQL Server (2005+) seem to be cleverer and may not require a sort, but I haven't dug too deeply (Edit: as per Mark Storey-Smith's updated answer)
In MySQL, I would still say that the index helps. Here is why:
There are several status variables that monitor bidirectional and random key traversal.
Here are those status variables:
Monitoring these variables will directly tell you of the presence of table scans (if the event indexes are not used), if ORDER BY ... DESC queries are present, and so forth.
IMHO, if Handler_read_last did not exist before MySQL 5.5, then ORDER BY ... DESC would have to be executed by full index scan and then executing Handler_read_prev for backward traversal.
Some detective work may be necessary in order to locate the specific queries (slow query logs, using --log-queries-not-using-indexes, general logs, etc).