Java Indexing
The single largest lever on query performance, and the one most often applied by superstition. Every index is also a cost on every write.
4 concepts · 12 interview questions
What this topic covers
Every concept in indexing, and the questions each one gets asked as. Where a question links, it has a full write-up.
How an index works
A B-tree holding sorted key values with pointers to rows, so a lookup is a few page reads instead of a scan of the table.
- How does a database index actually work?
- What is the difference between a clustered and a non-clustered index?
- Why does an index make writes slower?
Composite indexes and column order
An index on several columns is sorted by the first, then the second, so it can only be used from the left.
- Does an index on (a, b) help a query filtering only on b?
- How do you choose the column order in a composite index?
- What is a covering index?
When an index is not used
Low selectivity, a function applied to the column, an implicit type cast, or a leading wildcard all stop the planner using an index.
- Why would the database ignore an index you created?
- Is an index on a boolean column useful?
- What is cardinality, and why does the planner care?
Reading an execution plan
EXPLAIN shows what the planner intends; the plan's scan types and row estimates tell you where the time will go.
- How do you find out why a query is slow?
- What is the difference between a seq scan, an index scan and an index-only scan?
- Is a sequential scan always bad?