Why 90% of production databases aren't indexed properly

David M Williams
Wednesday, 18 February 2009 16:55

IT Industry - Market

In the screenshot below I’ve used Microsoft SQL Server to perform two queries. One calls up a particular employee based on the primary key which is a made up field that merely counts one to infinity. The second calls the same person based on their payroll number which is a different field (and which could/should have been the primary key.)

In the bottom half of the screenshot you will see the execution plan SQL Server used to return the results.

For the first query, it just had to look up the clustered index. Dead simple.

However, for the second query, SQL Server had to perform more work. It had to look up the payroll number in a non-clustered index, and then retrieve the row. This query, while returning the exact same result as the first query, took twice as long to execute.

In practice, the software never returns lists of employees by primary key value.

The generic list of employees is sorted by payroll number. And, specific lookups will be by either payroll number or surname. At no time does any operator – or the program itself – query the table according to the primary key.

This commercial application is wasting clock cycles, and wasting my time and your time, every time it queries the employee table. These nanoseconds add up. Spread them across lots of users, lots of queries, lots of days and weeks and months and lots of apps.

How much time is being wasted by poorly indexed databases around the world just because of one simple, simple thing – nobody bothered to tweak the clustered index?