Tuesday 3 March 2020

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