Missing Indexes

Think of a telephone directory

It shows everyone in the area, sorted by Surname and Firstname

If it was sorted by phone number, it would take an awfully long time to find Zachary Young

So that’s why it is sorted the way it is

A French phone directory is different

It’s sorted by Village, then Surname and Firstname

In database language, those are two different clustered indexes.  Clustered just means that the list of information is sorted that way

But suppose you were Detective Chief Inspector Tom Barnaby, every week hunting down the killer of half the population of the village of Midsomer.  You found a clue - a phone number scrawled in the victim’s blood at the murder scene, and wanted to find out whose number it was.  Yeah, obviously the murderer is going to turn out to be Zachary Young, but you don’t know that.  

Your database query might look something like this:

select

       firstname,

       lastname,

       address1,

       Town

from 

       Directory

where 

       PhoneNumber = '0123456789'

It’s going to take a long time to run that – because the directory table is missing an index on PhoneNumber

When you run the query, it has to search all the way through the list of names until eventually it finds a match

SQL Server keeps a record of all the queries it runs, and you can get it to count up all the times it scans through a table because the index it needs is missing.  

So – you REALLY need that missing index.  You will find the murderer eventually, but the index makes it a lot easier

Create the index, and everything works a whole lot more easily

Want something a bit more technical?  Here's how to find the missing indexes