Using index in ORDER BY clause

Question

I have read Indexing Order By section of "SQL Performance Explained" book. I have some questions about it. Consider we have a compound index on (a, b, c). Is my understanding correct?

SELECT * FROM tbl WHERE a = ? AND b = ? AND c = ? ORDER BY a, b, c: will use index for WHERE and ORDER BY
SELECT * FROM tbl WHERE a = ? ORDER BY b: will use index for WHERE and ORDER BY
SELECT * FROM tbl WHERE a = ? ORDER BY c: will use index for WHERE but will NOT use index for ORDER BY
SELECT * FROM tbl WHERE a = ? AND b = ? ORDER BY c: will use index for WHERE and ORDER BY
SELECT * FROM tbl WHERE b = ? AND c = ? ORDER BY a: will NOT use index for WHERE but will use it for ORDER BY
SELECT * FROM tbl WHERE a = ? AND c = ? ORDER BY b: I don't know this. Please help.
SELECT * FROM tbl WHERE c = ? ORDER BY b: will NOT use index for WHERE and ORDER BY

Gordon Linoff · Accepted Answer · 2014-08-29 16:17:19Z

4

Some databases have additional index usage, but in general, you are correct for the first 4 and the last one.

Item (5) depends on the engine and the characteristics of the data. There are two ways to process this query:

Select the right rows and order them.
Read through the ordered data and then do the filtering.

THe second uses the index, the first does not.

Item (6). The typical engine would use the index efficiently for the condition on a. What happens after that is up to the engine. This is similar to the situation for (5), where the engine could use the index for the sort but could also do the filtering first.

By the way, selecting only a, b, and c instead of * could affect the optimization options for (5) and (6).

answered Aug 29, 2014 at 16:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using index in ORDER BY clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related