Before you invest in database indexing

The version of this that works is simpler than the version most people imagine. Before you spend anything on database indexing, it is worth confirming a few things are already true.

Speed is a feature people notice only in its absence, and then they leave rather than complain. If it only works because one person remembers to do something, it does not work yet.

Prerequisites

• You can describe the outcome you want in one sentence
• Someone owns it after the work is done
• The right index turns seconds into milliseconds
• You have a way to tell whether it worked

Where it usually goes wrong

Every index also slows writes, so be deliberate. It is worth being explicit about, because assumptions differ quietly. Check it against what you would want a competitor's site to get wrong.

Look at the query plan instead of guessing. Small and consistent beats large and occasional here. It is the sort of thing that looks like polish right up until it costs you an enquiry.

How to tell if yours is fine

Real-world numbers from actual visitors matter more than a score produced on a fast laptop. Three things worth confirming about database indexing before you move on:

• Someone can say what the current setup is without going to look
• Look at the query plan instead of guessing — and you know whether that is true here
• There is a way to tell whether the last change to this helped

Most of the value here comes from doing the first two things, not all of them.