The Index Is Right There and the Database Will Not Use It
Last timeThe Other Two Shapes
Five reasons cover almost every case, and in two of them the database is right to refuse. Here is how to tell which one you have, in the order worth checking.
This is the lesson people arrive for. The index exists, it names the column in
the filter, and the query is still reading the whole table. Five causes cover
almost all of it.
An expression where the column should be
The first cause is the commonest and the easiest to fix. An index on a column
stores the values of that column, in order. If the filter does not ask about
those values, there is nothing to seek on.
| fixable by you | visible in the query tex | fixable without a new in | share of real cases, per | |
|---|---|---|---|---|
| an expression or a type | 1 | 1 | 1 | 42 |
| the filter matches most | 1 | 0 | 0 | 23 |
| the column is not leadin | 1 | 1 | 1 | 16 |
| the statistics are stale | 0 | 0 | 1 | 12 |
| the fetch costs more tha | 1 | 1 | 0 | 7 |
The forms this takes are worth listing because each looks innocent. Wrapping
the column in a function, so the filter asks about the uppercase version of a
name while the index holds the name. Arithmetic on the column, so the filter
asks about a price plus tax. A comparison against a different type, where the
database quietly converts the column rather than the constant. And a leading
wildcard in a text match, which asks for keys with something in the middle,
which sortedness cannot help with.
The fix in every case is to move the computation off the column. Compare the
column against a computed constant instead of comparing a computed column
against a constant, make the types match, and where the expression is genuinely
needed, build the index on the expression itself, which most databases support
and which simply sorts by the computed value instead.
The lesson stops here
5 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents