DATABASES
Indexing Best Practices
A short guide for dealing with database indexes
Database indexes are often designed badly. The power that database indexes have is realized only if they are designed and used efficiently. Otherwise, an index is a sheer wastage of disk space and database performance. But you don’t want to waste disk space, so let’s quickly go through some of the things that you need to do for designing and using indexes properly.
Database indexes should be created for one reason only — to serve an existing or a future load of queries on the database, that is, the Indexes have to be designed based on current or expected usage. All major databases offer some way to monitor index usage.
Use the WHERE Clause
The WHERE clause in SQL queries filters data. Indexes help in filtering data faster as the data is stored in a predefined order based on some key columns. If the query uses those key columns, the index will be used, and the filter will be faster.
The existence of an index doesn’t guarantee its usage when key columns are referred in the WHERE clause. So, you have to make sure that index is being used. Most databases have a component called the Query Engine, which decides on which of the many execution plans be used to ensure the…
