MySQL Indexes: What you (probably) didn't know

Silviu Tanasa is a software engineer at hackajob working on the product team. When he’s not creating world-class code, he can be found spending quality time with his family.

Today we’re going to answer a couple of burning questions: what are database indexes and how are they useful? Whilst they may look simple (e.g. the more you have, the faster your table searches will be), the reality isn’t quite as easy. We’ve written a short outline and created a simple tutorial for you to go through - let’s take a look under the hood.

First, let’s start with the definition of a database index:

‘An index is an ordered subset of table columns, with each row entry pointing to its corresponding table row, improving the speed of operations in a table. Adding indexing to your MySQL database gives you three main advantages:

Query optimisation

Uniqueness

Text searching

These advantages come from the four different types of indexes: primary, unique, normal and full-text. In MYSQL, these are used for the following operations:

To find the rows matching a WHERE clause quickly

Eliminating rows from consideration

Retrieving rows from other tables when performing joins

To find the MIN() or MAX () value for a specific indexed column

To sort or group a table

Ok? So far, so good. Assuming that most of you already know about primary and unique indexes, we also want to reveal some features about normal indexes when applied to multiple columns, as well as when and how to use index hints.

Multiple-Column normal indexes are useful when you have a number of columns within a query. Let’s suppose that you want to do this to a users table like so:

In order to improve the query performance, you decide to add a normal index on these columns (in this case: first_name, last_name, email). You might be thinking, ‘great! I have a big index with a query optimisation’, but it’s more than likely that you are taking this method for granted.

The same scenario applies if you search for only the last_name:

or for only the last_name and email:

This is because you have a large index on the three columns.

Here, the tricky part is that the multiple-column indexing approach is based on a strategy known as LEFTMOST PREFIXING, which states that for an index composed of the following columns: (A,B,C), the following combination will be used: (A); (AB) and (A,B,C). This means that looking for only ‘last_name’ and ‘email’ will not benefit from the query optimisation given by the index set on the group (A,B,C).

A special use case for the multiple column index is the covering index. An index that includes all the columns retrieved by a query, the covering index has a great (and underestimated) power using values from the index structure to find the full table rows instead of using the index values as pointers. The covering index provides significant cost reduction:  

Given the right query, any column index or multiple-column index can act as a covering index. Remember that it’s best practice to design your indexes and queries to take advantage of this kind of optimisation wherever possible.

Please note that if you are using the InnoDB engine on your database, this technique will be applied to more indexes than if you had used MyISAM, because InnoDB secondary indexes also include the primary key columns.

Remember to take into consideration the following drawbacks of the covering indexes:

Because they duplicate data from the original table, the space cost will increase

MySQL can decide to stop using the current covering indexes at any time, especially if queries change quickly in the near future

Index Hints give the MySQL optimiser information about how to choose indexes during query processing, applying to SELECT statements only.

The USE INDEX (index_list) and IGNORE INDEX (index_list) are incredibly useful when needing to control how MySQL uses the named indexes to find rows in the table. Ideal if EXPLAIN shows that MySQL is using the wrong index from the possible list of indexes, another option is to use FORCE INDEX which acts like USE INDEX (index_list), and forces MySQL to use a table scan as long as there is no other way to use one of the named indexes to find rows within the table.

Another way to get fine-tuned control over the optimiser selection is to add a FOR clause to the hint. If you only wish to affect the indexes used when MySQL decides how to find rows in the table as well as how to process joins; make sure to use FOR JOIN. For influencing index usage for sorting or grouping rows, use FOR ORDER BY or FOR GROUP BY.

That’s all for now, but make sure to stay tuned for our next article regarding full-text indexes.

Was our tutorial useful? Make sure to visit our website for more info on some of our candidate challenges.

Article by Silviu Tanasa