Last night I did an amazing thing. A mysql query was taking around 20-30 minutes to execute. It was a pretty heavy query with the purpose of creating a dump of data in excel format. With 3-4 JOINs, 2-3 WHERE clauses. And it was already optimized, and it seemed like there is nothing we can do. But then an idea hit me. And the query is now happily dumping data in 3-4 seconds.
So what did I do? Some of you might have guessed it. Yes it was the indexes. I just added few indexes to the tables that were used in that query, and voila! Magic happened.
So, what are these index things you talk about?
From MySQL documentation:
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.
If you have ever created a Primary Key, Foreign Key then you have used indexes.
So, how does mysql uses indexes?
Again from the docs:
- To find rows matching a where clause in query
- To get rows based on a join
- To sort rows, in GROUP BY & ORDER BY operations
So, how do we add this index thing?
Mainly there are 3 ways to add index
- Using Primary keys
- Using Foreign keys
- Using Column or Multi-Column Indexes
Many of you might not be aware but by creating primary keys & foreign keys you are creating indexes in your table, and making your database a better place to query data from. Also, if you just create a primary key and don't use it where clauses if your query, then you are utilizing those indexes.
3rd type of index, i.e. column index is a index that you need to create explicitly. If you see a column being used in WHERE, JOIN, GROUP BY or ORDER BY clauses, adding a index to that column will reduce the execution time of your query. As the database will be optimized for querying record using that column. Creating a column index is easy. Just use
ALTER TABLE table_name ADD INDEX index_name (column_name)
So, next time you see a query just taking too much time. Put a index on it.
You see a table with huge data, where retrieval is done on non-key columns (like name in user table). Put a index on it.
Note: Make sure your index_name is unique across the database, and it's not same as any other database object like table or procedure. Because, while deleting an index you might accidentally drop a table if the name collides.
This is just a small introduction to indexes in databases. Not many people use indexing in database, they might start, after reading this article.
For further reading: