CODEWITHSUNDEEP
sqldatabaseperformancesql-execution-plan
Majid Azimi
Majid Azimi

Reputation: 5745

Using index in ORDER BY clause

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?

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

Upvotes: 4

Views: 117

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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).

Upvotes: 4

Related Questions