Understanding Indexes in MySQL: Part Three

This blog post is the third part of the series of blogs about indexes in MySQL. In the second part of the blog post series about MySQL indexes, we covered indexes and storage engines and touched upon some PRIMARY KEY considerations. The discussion included how to match a column prefix, some FULLTEXT index considerations, and how you should use B-Tree indexes with wildcards and how to use ClusterControl to monitor your queries’ performance, subsequently, indexes. 

In this blog post, we will go into some more details about indexes in MySQL: we will cover hash indexes, index cardinality, index selectivity, we will tell you interesting details about covering indexes, and we will also go through some indexing strategies. And, of course, we will touch upon ClusterControl. Let’s begin, shall we?

Hash Indexes in MySQL

MySQL DBAs and developers dealing with MySQL also have another trick up their sleeve as far as MySQL is concerned – hash indexes are also an option. Hash indexes are frequently used in the MEMORY engine of MySQL – as with pretty much everything in MySQL, those kinds of indexes have their own upsides and downsides. The main downside of these kinds of indexes is that they are used only for equality comparisons that use the = or <=> operators meaning that they’re not really useful if you want to search for a range of values, but the main upside is that lookups are very fast. A couple more downsides include the fact that developers cannot use any leftmost prefix of the key to find rows (if you want to do that, make use of B-Tree indexes instead), the fact that MySQL cannot approximately determine how many rows there are between two values – if hash indexes are in use, the optimizer cannot use a hash index to speed up ORDER BY operations either. Bear in mind that hash indexes are not the only thing the MEMORY engine supports – MEMORY engines can have B-Tree indexes too.

Index Cardinality in MySQL

As far as MySQL indexes are concerned, you might also heard another term going around – this term is called index cardinality. In very simple terms, index cardinality refers to the uniqueness of values stored in a column that uses an index. To view the index cardinality of a specific index, you can simply go to the Structure tab of phpMyAdmin and observe the information there or you can also execute a SHOW INDEXES query:

mysql> SHOW INDEXES FROM demo_table;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| demo_table |          1 | demo     |            1 | demo        | A         |      494573 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

The SHOW INDEXES query output that can be seen above as you can see has a lot of fields, one of which depicts the index cardinality: this field returns an estimated number of unique values in the index – the higher the cardinality, the greater the chance that the query optimizer uses the index for lookups. With that being said, index cardinality also has a brother – his name is index selectivity.

Index Selectivity in MySQL

An index selectivity is the number of distinct values in relation to the number of records in the table. In simple terms, index selectivity defines how tightly a database index helps MySQL narrow the search for values. An ideal index selectivity is the value of 1. An index selectivity is calculated by dividing the distinct values in a table by the total number of records, for example, if you have 1,000,000 records in your table, but only 100,000 of them are distinct values, your index selectivity would be 0.1. If you have 10,000 records in your table and 8,500 of them are distinct values, your index selectivity would be 0.85. That’s much better. You get the point. The higher your index selectivity is, the better.

Covering Indexes in MySQL

A covering index is a special kind of index in InnoDB. When a covering index is in use, all the required fields for a query are included, or “covered”, by the index meaning that you can also reap the benefits of reading only the index instead of the data. If nothing else helps, a covering index could be your ticket to improved performance. Some of the benefits of using covering indexes include:

• One of the main scenarios where a covering index might be of use include serving queries without additional I/O reads on big tables.

• MySQL can also access less data due to the fact that index entries are smaller than the size of rows.

• Most storage engines cache indexes better than data.

Creating covering indexes on a table is pretty simple – simply cover the fields accessed by SELECT, WHERE and GROUP BY clauses:

ALTER TABLE demo_table ADD INDEX index_name(column_1, column_2, column_3);

Keep in mind that when dealing with covering indexes, it is very important to choose the correct order of columns in the index. For your covering indexes to be effective, put the columns that you use with WHERE clauses first, ORDER BY and GROUP BY next and the columns used with the SELECT clause last.

Indexing Strategies in MySQL

Following the advice covered in these three parts of blog posts about indexes in MySQL can provide you with a really good foundation, but there are also a couple of indexing strategies you might want to use if you want to really tap into the power of indexes in your MySQL architecture. For your indexes to adhere to MySQL best practices, consider:

1. Isolating the column that you use the index on – in general, MySQL does not use indexes if the columns they are used on are not isolated. For example, such a query would not use an index because it’s not isolated:

   SELECT demo_column FROM demo_table WHERE demo_id + 1 = 10;

   Such a query however, would:
    

   SELECT demo_column FROM demo_table WHERE demo_id = 10;

    

2. Do not use indexes on the columns that you index. For example, using a query like so would not do much good so it’s better to avoid such queries if you can:
    

   SELECT demo_column FROM demo_table WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(column_date) <= 10;

    

3. If you use LIKE queries together with indexed columns, avoid putting the wildcard at the beginning of the search query because that way MySQL will not use an index either. That is instead of writing queries like this:

   SELECT * FROM demo_table WHERE demo_column LIKE ‘%search query%’;

   Consider writing them like this:

   SELECT * FROM demo_table WHERE demo_column LIKE ‘search_query%’;

   The second query is better because MySQL knows what the column begins with and can use indexes more effectively. As with everything though, the EXPLAIN statement can be of great help if you want to make sure your indexes are actually used by MySQL.

Using ClusterControl to Keep Your Queries Performant

If you want to improve your MySQL performance, the advice above should set you on the right path. If you feel that you need something more though, consider ClusterControl for MySQL. One of the things that ClusterControl can help you with include performance management - as already noted in previous blog posts, ClusterControl can also help you with keeping your queries performing at the very best of their ability all the time - that’s because ClusterControl also includes a query monitor that lets you monitor the performance of your queries, see slow, long-running queries and also query outliers alerting you of the possible bottlenecks in your database performance before you might be able to notice them yourself:

You can even filter your queries allowing you to make an assumption if an index was used by an individual query or not:

ClusterControl can be a great tool to improve your database performance while taking the maintenance hassle off your hands. To learn more about what ClusterControl can do to improve the performance of your MySQL instances, consider having a look at the ClusterControl for MySQL page.

Summary

As you can probably tell by now, indexes in MySQL are a very complex beast. To choose the best index for your MySQL instance, know what indexes are and what they do, know the types of MySQL indexes, know their benefits and drawbacks, educate yourself on how MySQL indexes interact with storage engines, also take a look at ClusterControl for MySQL if you feel that automating certain tasks related to indexes in MySQL might make your day easier.