To Index or not to Index

Adding indexes to a database incurs an overhead every time you INSERT, UPDATE or DELETE data that the indexes reference and slows the processing down.
Adding indexes to a database speeds up the SELECT queries that can use those indexes to directly access the requested data.

One of the above statements suggests that you should avoid using indexes because it slows things down while the other statement suggests that indexes speed things up. That they are both right is due to the fact that each is talking about a different situation. We need to keep both of these alternate situations in mind when determining whether or not to create each specific index for our database.

We know that each index that we add will slow down any updating of the database that involves the field(s) in the index and so what we need to determine is the frequency of those updates as compared to the frequency of the queries on the data that having the index there will speed up. If the time saving from the faster queries will be greater than the time cost of updating the index when the data chancges then that index is worth having because overall it will make the database run faster.If the index will seldom be used in queries but will need updating a lot as the data in the database changes then getting rid of that particular index will make the database run faster.

To determine what indexes to add/remove to improve the performance of your database you need to consider the queries that will be run against that data. If few queries are ever going to be run then having indexes will most likely slow things down. As most applications generally run far more queries than they do updates you will generally find that adding the right indexes to your database will make it faster.

In determining what fields to index you need to look at the WHERE clause of the queries to see which fields it is referencing. To start with you can ignore any comparisons that do not involve the entire field in the database. If the query states WHERE MONTH(report_date)=4 then there is no way that adding an index on report_date can speed up this query because the values in the index will not be in month order and so the index will not make finding the required records any faster.

The most important thing with indexes is selectivity. The fewer records that need to be retrieved from the database the more effective the index.If you have a gender field in your database to identify whether people are male or female then placing an index on that field will be useless as approximately 50% of the entries in the database will be a match for the required value and so a sequential read through the database can retrieve those that match relatively quickly without the index. The overhead of having the index will in that instance be greater than the near zero benefit that having the index would provide. The fewer records in the data that are expected to have the same value the greater benefit that an index will provide as the fewer records that will need to be read from the database when the index is used to go directly to those records.

The ORDER BY clause will also affect the usefulness of compund indexes. An index on last_name, first_name will speed up queries where you want the names in that order or where you are just retrieving last name but will not help if you want the data in first_name order. Of course if you will never need the data in first name order except within a given last_name then using a compound index on both fields will be far more useful than having two separate indexes one on each field.

The idea is to implement those indexes that will speed up the database processing while not implementing those that will slow it down. Unfortunately you may not be able to tell in advance just what the actual data will look like and so will not know whether an index will actually be selective enough to be useful. In that situation you may need to add or delete indexes at a later date once there is actually enough data in the database to more accurately determine the usefulness of the various indexes.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow