, ,

A reporting system I’m working on had a notably slow-running query. Run time was on order of 42 seconds. I applied indexes to the tables, and run time dropped to on order of 1 second. That’s a 4200% improvement, with no changes to logic required.

Indexes are trivially simple to implement in most DBMSs, and give a huge performance boost if used correctly.

There are 2 types of indexes: clustered and non-clustered. Clustered indexes physically reorder the rows so that the indexed columns are in order. This makes them especially helpful when you select ranges of values from a table. Non-clustered indexes simply create a lookup table of indexed values to physical locations.

Indexes can slow down inserts, so if you have a huge volume of transactions going into a table, you may want to leave it unindexed.

Indexes also degrade over time, so you should periodically update them. This is typically done by the database administrator, but on small projects there may not be a dedicated database administrator. It’s generally a straightforward operation. Just script it and run it once a day, once a week, etc, depending on need.