intro
Let’s learn about the most frequent mistakes connected to MySQL indexing. Explore methods to supercharge your MySQL instance using indexes!
Query performance is at the top of the mind of every developer and one thing that has always helped improve it was indexes — developers and DBAs turned to indexes when they needed to optimize an SQL query running through billions of records or when JOIN SQL queries started going sour.
Whatever your use case is, MySQL indexing is something for everyone, but improper indexing is also home to a couple of rookie mistakes that, if not dealt with properly, can cause your database (and your application) to crash and burn in the process.
Let’s explore these mistakes and see how to avoid them!
Types of Indexes in MySQL
Unfortunately, in many cases, the documentation of MySQL isn’t very clear when it comes to indexing. Sometimes, to get the gist of what’s happening within the documentation, you have to be educated about what the internals of your database contains and how indexing works in the first place.
For those who know their way around index types in MySQL, documentation is an awesome place, however, those who don’t may find things a little harder to come by.
MySQL has the following index types:
Aside from index types, one other common cause of concern is deciding when and what data to index. That’s not rocket science for many experienced DBAs, but decisions like that can indeed drown many developers in the woods, so we’ll get into that too.
What and When to Apply MySQL Indexing?
The best way to assess what data should be indexed is to look at what data your queries are accessing in the first place. Inspect your queries — if you see a SELECT query with a demo column after the WHERE clause, add an index on it, run it through an EXPLAIN clause (examples below) and see how your database uses it. That’s the key to MySQL indexing.
Aside from deciding what to index, another important question is when to index in the first place for your queries to obtain (and maintain) maximum performance for your use case. Consider indexing your data when:
Index your data once your database starts to show the first signs of discontent in relation to the points above — or when you have a gut feeling to index a column or two. That works too.
Most Common MySQL Indexing Mistakes
Before you index your data, be aware of MySQL indexing mistakes — these mistakes won’t “eat” your data, database, or anything connected to them, but they can certainly cause the performance of queries acting on your data to dive into the ground. The worst indexing mistakes you can do to your database are as follows:
Another mistake has to do with things developers do before building indexes. This is where MySQL indexing comes into place — an index for your use case is easy to create if you:
See how both of those points connect with what we’ve said above? To properly avoid making mistakes in the indexing realm, you have to understand how your database thinks when working with indexes, too. And that’s where the SQL EXPLAIN clause comes into play.
Utilizing the EXPLAIN Clause for Indexes
To unleash the full power of the EXPLAIN clause for MySQL indexing, we first have to understand what we’re working with in the first place. For that, SHOW INDEXES FROM [table_name] will help (make sure to select the database you’re running the query on before executing it too):
Alright, we’re lying — that’s the ghetto way of doing things. Flick back to DbVisualizer — the database client with the highest user satisfaction in the market — then select your database, the table you’re working with, and you will be able to observe all of the indexes existing on this table in a much nicer fashion:
Once you understand what indexes exist on a table, craft a query that you would use to assist your use case (make sure to have a column or two after the WHERE clause to ensure that the query is using indexes.) Once your query is ready, add an EXPLAIN clause in front of it so it looks like so:
1
EXPLAIN SELECT * FROM `demo_table`
2
WHERE `username` = 'Demonstration' AND `email` LIKE 'sample%';
Then, observe the output of the EXPLAIN clause. It should return the following:
For the purposes of this blog, when avoiding MySQL index mistakes, you’d be mostly interested in the values under the possible_keys, key, and key_len columns: these columns define what kind of index was considered for use by your database (possible_keys), what index was used by your query, if any (that’s the key column), and what’s the length of the index (key_len).
Provided our demo_table has a primary index on id , a descending index on username, and a partial index on email, these types of indexes could be used when:
We’re making use of the username column after the WHERE clause (query #1):
We’re looking for infomation on rows bearing a specific ID (query #2):
Or we’re looking for information pertaining to email addresses. Here’s where index cardinality comes into play as well — if our index cardinality (the amount of unique values within the index) is rather low, our database may not be able to use the index in the first place:
All this means that we have another thing to worry about after creating our index: we have to ensure that the amount of unique values in the index is high enough to necessitate our database looking through them.
There are other things at play, too, however, we’ll leave those for you to figure out. With that being said, there are resources that can help you with this, and the book “Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case” is one of them.
The book focuses on how developers “break” their databases to necessitate optimization in the first place, and how to secure it after you optimize your database too, so it will bring a refreshing standpoint to the way you look at your databases.
Summary
This blog has walked you through a bunch of indexing mistakes on MySQL, with the most important of those being related to understanding index types, ensuring you have enough data to necessitate indexing in the first place, and consulting with the EXPLAIN clause. Perform those actions and your database will surely thank you — and if it doesn’t, read a book or two to ensure that it will.
FAQ
What index should I choose when working with MySQL?
The most frequent type of index in MySQL is the B-Tree index, however, there are other types, so to make a decision, evaluate the use cases they assist with carefully and make a wise decision based on your use case, the problems you face, and your experience dealing with MySQL-specific issues.
How many indexes should a single table have?
That depends on your use case. Don’t overdo things — if you index every column in a table, you’re probably doing it wrong and should look into storage engines that do it for you instead — but if you understand how indexes work after consulting the EXPLAIN clause, the solution should present itself to you pretty soon. To learn specific things like these, we recommend digging into books specializing in these topics.
Are there other ways than MySQL indexing to optimize queries?
There sure are — while we won’t be able to list every optimization technique in a single answer, consider partitioning your tables if your use case necessitates that, normalize your database, and only select data that you need to keeping in mind that queries are tasks composed of other tasks. That should be a good starting point. MySQL indexing is not the only solution!
