The story began when I was assigned a task at the company, and it was decided that I should optimize one of the project’s sections, which had very heavy queries on large datasets in MySQL and took nearly 30 seconds to load. My first step was to go for indexing the columns of the tables.
I must say that the speed increased dramatically, and the load time was reduced to 10 seconds, meaning a 20-second improvement in the application’s performance. This excited me to write about this topic in this article, discussing how MySQL enhances query speed through indexing.
What is Indexing?
Imagine walking into a vast library with thousands of books, and you need to find a specific book. If you start searching one by one through the shelves, it could take days. However, if there’s a guide indicating which shelves are organized alphabetically and which ones are categorized based on your preferred genre, you can find your book within minutes. Indexing essentially does the same thing.
MySQL Indexing behind the scene
MySQL uses a B-Tree structure to operate indexing. When you index a column, it’s like organizing the data in that column into a tree structure, with a main starting point at the top. As you go down this tree, the data gets sorted based on lower-level branches. Think of it like creating a tree where each branch knows about the branches beneath it.
So, when you search for something in that column in MySQL, it starts at the top of the tree. At each level, it figures out which branch to go down to find the specific data. Eventually, it reaches the lower branches where the exact data is stored and brings it back.
In simpler terms: Indexing in MySQL makes searching for data much faster. It’s like using a well-organized tree where, at each step, a lot of unnecessary data is filtered out, saving time compared to checking every single piece of data one by one.
Mysql B-Tree Structure
The structure in the picture above shows how exactly a B-Tree is created. you can index your column by the following code:
CREATE INDEX index_name
ON table_name (column1, column2, ...);Can we index all columns to increase speed?
Now that we understand how indexing a column boosts speed, why not index all our columns to make our program even faster? Well, that’s not a good idea for a couple of reasons.
Firstly, each time we index a column, MySQL creates a tree of the data in that column and stores it in memory. So, the more columns we index, the more space MySQL occupies in memory. Also, building these trees takes time, and the more trees, the more time it takes.
Secondly, indexing speeds up ‘select’ operations on that column but slows down ‘create,’ ‘update,’ and ‘delete’ operations because each time these queries are executed, the tree needs to be updated, which adds extra overhead.
That’s why we should only index columns that are frequently used in searches to balance performance.
Conclusion
Indexing columns in a database can be a great solution to boost query performance. However, it’s important to note that it’s not the only solution. First and foremost, you should optimize your queries as much as possible. Techniques like lazy loading, eager loading, eliminating unnecessary joins, and other approaches can significantly enhance your program’s performance. Remember, managing a database is an art, and to write good code, you need to have a solid understanding of how the database operates. So, while indexing is helpful, it’s part of a broader strategy to achieve optimal performance.
