Best practices to follow with database indexes

Recently while working on a recommendation system, it seemed to take forever to run a simple query. And to  make matter worse, there were several queries to run. The system was working fine when we have thousands of records. However, in the long run, when the data grew and thousands of rows became millions, then the system started to perform poorly. Searching and Sorting became nightmare.

I simply added indexes on searching (WHERE) and sorting (ORDER BY) as well as Grouping and Aggregating columns. Then the system started running smoothly.

By creating correct  indexes for tables based on the queries your applications use, you can improve performance of your application.

Though indexing is such a large topic to discuss in this small post , lets talk about basics of Database Indexes.

What is a database index?

A database index is a data structure, associated with a Table (or View), used to improve queries execution time during retrieving the data from that table (or View). It requires its own disk space and holds a copy of the indexed table data.

Index can be built using CREATE INDEX statement.

# Creating Index
CREATE INDEX index_name
ON table_name (column1, column2, ...);


# Creating Unique Index
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Index can quickly retrieve data from a table containing considerable amount of records. However, you should also consider the cost associated with creating and maintaining an index structure. The more indexes a table has, the faster SELECT statements, but slower INSERT, UPDATE, and DELETE statements.

It is important to consider how the data is being used while creating index. You would index columns you search frequently, columns that you sort and Foreign Keys.

Best Practice for Database Indexes

1. Create indexes based on use.
2. Index Search (WHERE) columns.
3. Index Sorting (ORDER BY) columns.
4. Index Grouping and Aggregate Columns
5. Index on all Foreign Keys
6. Index Unique Column (Primary Key)
7. Use Clustered Index for large tables
8. Remove unused indexes

These are the basic rule of indexing. I will keep on updating this post.

Thanks for reading.