Unused Indexes in MySQL: A Basic Guide

Introduction

Properly designing and using indexes available in multiple flavors of MySQL can be a challenge due to the variety of their types and all of the nuances unique to them. However, don’t fret – this blog post should act as a decent starting point for people wanting to learn how MySQL uses indexes on a deeper level.

What Are Indexes and When Should We Use Them?

In their most basic form, database indexes act as a list of records—records that have a reference to a database structure in which they reside. Indexes are mostly used to eliminate data from consideration, and by doing so, make queries faster.  Without an index, a database must read all data that exists in a table one row at a time, meaning that if our table has, say, 100,000 records, a database must read through the majority of them until it finds the row suitable to us. With an index, however, a database can eliminate certain rows from consideration, scan through fewer rows, and return results faster.

Indexes have multiple types – each of them is suitable for different things; for example, B-Tree indexes might suit us well if our primary queries use the equality operator (=), more than (>), more than or equal to (>=), less than (<), less than or equal to (<=) operators or BETWEEN keywords, and, in some specific cases, LIKE queries. 

Prefix indexes might be useful when the data stored inside our columns is pretty large, and as such, indexing the entire column might be out of the question, etc. Since we have already covered some of the subtleties of indexes in MySQL, we won’t go too much into detail in this blog post, but understanding the following should help:

Above, you can see some of the most basic and frequent scenarios where developers could use indexes. The general idea about indexes is that indexes speed up SELECT queries while slowing all of the other types of queries down – they slow other types of indexes down because the database needs to do some extra work – for example, when inserting data into the table, the database has to add a new record to every index on a certain table. Multiply that by the number of indexes you have, and you already see how quickly the performance of some types of queries could degrade.

Now that we have gone through some of the basics, let’s get down to business – what can we do to make our indexes actually necessary to MySQL?

Making Sure MySQL Uses Indexes

If we want to make sure our MySQL instances use the indexes we feed them, we have a couple of options:

To create indexes that would be useful for your MySQL instances, one of the things you need to take care of are the index types that you are going to use – however, you also need to keep an eye on how many indexes are you going to use and create a tradeoff between the slow performance of INSERT, UPDATE, and DELETE queries and faster performance of SELECTs that would be acceptable to you. To do that, first, add an index to your MySQL instance – Arctype can be of assistance here as well – navigate towards the table you want to add indexes on towards the left-hand side and click “Edit Table”: if you want to add a unique index to it, for example, click the button underneath the Constraints and edit them (in this case, checking “Unique” should do the trick):

Once you‘re done, click Apply, and your changes should be visible on your database instances:

Arctype will also kindly let you know once your changes are live:

Once you have added relevant indexes to your database, it’s time to let EXPLAIN do its magic. Connect to your database instance, then add EXPLAIN in front of your query to see what it does and whether your query uses the indexes you defined or not:

EXPLAIN SELECT * FROM arctype WHERE demo_column = 'Demo Data';
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | arctype | NULL | const | demo_column | demo_column | 602 | const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

To get the best chances of making your column useful to MySQL, you would need to complete the following steps:

1. Only query the column that you have indexed – doing so will attract the best chances of success. In other words, make sure the column you are querying is isolated – leave everything after the WHERE clause alone and do not mix anything into it, also avoid making an indexed column a part of an expression or a function within a query. As an example, avoid issuing queries like SELECT * FROM demo_table WHERE column_1 = ‘Demo’ AND column_2 = ‘Demo 2’; if column_2 is not indexed and only column_1 is, etc.
2. Look for the "Using index" value in the Extra column – that’s MySQL telling us whether the index is being used or not.
3. If you want to look into everything further, be sure to explore the possible_keys, key, and key_len columns as well: the possible_keys column depicts which indexes MySQL could choose from, the key column covers the index that was actually chosen and the key_len depicts the length of the index (in other words, the key).  Since you no longer worry about the SQL clients you use to run your queries on (you’re already using Arctype, aren’t you?), that’s not very complex.

Index Corner-Cases

In general, by now, you have probably understood that to make your indexes necessary, you should choose their types very carefully and also, where possible, use EXPLAIN queries, meaning that the aforementioned advice should put your databases and your indexes on a good path. However, a big part of indexes becoming obsolete and unnecessary is that MySQL is not very picky; it will generally take anything we throw at it. The same pertains to indexes – if you have 10 rows and decide to index them all, you can, and MySQL will definitely be able to complete your request, but what’s the point? In that case, adding an index would be a waste of disk space.

You should avoid always resorting to indexes to improve performance – partitions and other features can do some wonderful work as well. If that doesn’t solve all of your issues, consider normalizing your database schemas – that should get you one step closer to database perfection. If that doesn’t get you very far either, consider exploring both the MySQL’s and MariaDB’s documentation: that should do the trick.  Happy indexing!