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:

Scenario	Would Indexes Help Us?
Our application is primarily running SELECT queries.	Yes
We frequently update, delete or insert data to or from our database without doing many read operations.	No
We are dealing with big data sets and want to search through the data (for example, we want to build a search engine that would allow us to search through huge troves of data.)	Yes
We are dealing with big data sets and our application acts as an archive for information without using search features.	No

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:
​

Option	Information
Manual approach	Involves manually inspecting the table structure, the information regarding indexes through phpMyAdmin or other management tools, etc.
Using EXPLAIN queries provided by MySQL	EXPLAIN queries would provide us with relevant information that would help us figure out whether our queries use indexes or not. We would need to look into the possible_keys, key, and key_length columns. The possible_keys column would display all of the possible indexes MySQL was able to choose from, the key column would display the index actually chosen, and the key_length column would display the length of the index chosen by MySQL.

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](https://arctype.com/blog/mysql-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!

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com

​