6.5.7 How to add B-TREE index to DB
Let’s look at a practical example of optimizing a table with millions of rows using a simple index.
Additionally: we have detailed instructions on using indexes to optimize a database. You can read it at address.
Analysis
For analysis, we will need phpMyAdmin and familiarize ourselves with our database.
We have a database testdata with a single table of the same name. Now it’s worth looking at the structure of the table itself:
As we can see, we have ~3533441 rows. A full enumeration of such a table will take an unforgivably long time by the standards of working with a database.
Let’s check for indexes. You can do this in the Structure tab:
No indexing (Primary Key is a technical index by column id). Let’s try to execute a query and see how long a full enumeration will take:
2 seconds is an unforgivably long time for such a query. Let’s try to reduce this time.
Adding an index
So, what type of index should we add and how?
Based on the article, our choice will fall on the simplest BTREE.
Let’s add it to the table with the following query:
ALTER TABLE testdata ADD INDEX hash(hash) USING BTREE;
Please note: creating an index can take a significant amount of time. It depends on the table itself and the complexity of the index.
Testing
Time to check the speed of executing a similar query after adding the index:
As you can see, the query was executed in 0.0013 seconds. This is more than a thousandfold speedup.
Of course, this test table was synthetically created specifically to reproduce such a result. In real conditions, a thousandfold speedup of queries can not always be achieved.
