Understanding Indexes in MySQL: Part Two

This blog post is the second part of the series of blogs about indexes in MySQL. In the first part of the blog post series about MySQL indexes, we have covered quite many things, including what they are, what they do, what their types are, how to choose optimal data types, and MySQL character sets for indexes that you use. We went through the benefits and drawbacks of using indexes in MySQL; we told you how to choose the best index to use, how to improve query performance and make sure MySQL uses your indexes, how many indexes should you have. We also went through some considerations related to storage engines. This blog post will go into more detail regarding some of the content we have discussed in the first part of the series. We will start with the correlation between indexes and storage engines in MySQL.

Indexes and Storage Engines in MySQL

As we have already mentioned in a previous blog post, there might be some kinds of limitations to indexes and other things if you use certain storage engines in MySQL. Here are some of them – we will now define what some of them are (some of them were covered in the first part of the blog series so if we’re missing something it’s probably in there), then cover them with a more in depth analysis:

• As per the MySQL documentation, the maximum number of indexes, the maximum key length and the maximum index length is defined per storage engine. As we have already mentioned in a previous blog post, the maximum number of indexes per MyISAM and InnoDB tables are 64, the maximum number of columns per index in both storage engines is 16, the maximum key length for InnoDB is 3500 bytes and the maximum key length for MyISAM is 1000 bytes.

• You cannot use CREATE INDEX to create a PRIMARY KEY – use ALTER TABLE instead.

• BLOB and TEXT columns can be indexed only for tables running the InnoDB, MyISAM and BLACKHOLE storage engines.

• If you only index a prefix of the column, keep in mind that the prefix support and their length are also dependent on storage engines. A prefix can be up to 767 bytes long for InnoDB tables that use the REDUNDANT or COMPACT row format, but for DYNAMIC or COMPRESSED row formats the prefix length limit is increased to 3072 bytes. For MyISAM tables, the prefix length limit is 1000 bytes. The NDB storage engine does not support prefixes at all.

• If a strict SQL mode is enabled and the index prefix exceeds the maximum column data type size, CREATE INDEX throws an error. If a strict SQL mode is not enabled, CREATE INDEX produces a warning. If a UNIQUE INDEX is created, an error occurs.

• In general, MySQL only allows you to create up to 16 indexes on a given table.

• If you are using a PRIMARY KEY index, you can only have one primary key per table. FULLTEXT, UNIQUE INDEXes, and INDEXes do not have this limitation.

•  If you are using FULLTEXT indexes, bear in mind that they only can be used for InnoDB or MyISAM storage engines and for CHAR, VARCHAR or TEXT columns. Also keep in mind that MySQL only uses FULLTEXT indexes when MATCH() AGAINST() clauses are used and that you can actually have an index and a fulltext index on the same column at the same time if you so desire and that FULLTEXT indexes have their own set of stopwords each specific to storage engines in use.

• B-Tree indexes may be useful if you use LIKE queries that begin with a wildcard, but only in certain scenarios.

Knowing these index limitations should prove to be useful if you are trying to understand how indexes in MySQL work. What’s even more important to understand though is the fact that you must verify that your indexes are actually used by MySQL. We have touched on this briefly in the first part of these series (“How to Choose the Best Index to Use?”), but we haven’t told you how to verify that your indexes are actually used by MySQL. To do that, verify their usage by using EXPLAIN – when EXPLAIN is used together with an explainable statement, MySQL displays information from the optimizer about the execution plan of the statement.

PRIMARY KEY Considerations

Some of the basic considerations relating to PRIMARY KEY indexes in MySQL include the fact that they are primarily used to uniquely identify records in a table and are frequently used with AUTO_INCREMENTing values meaning that they can be very useful if you are creating, say, ID fields. PRIMARY KEY fields must contain unique values and they cannot contain NULL values.

Matching a Column Prefix

Indexes can also match a column prefix. This approach to indexes can be useful if your columns are string columns and you think that adding an index on the whole column would potentially consume a lot of disk space. Your indexes can match a column prefix like so:

ALTER TABLE demo_table ADD INDEX index_name(column_name(length));

The above query would add an index index_name on a column named column_name only for a column prefix defined. To choose a good amount of length to index, make sure that your use of the prefix maximizes the uniqueness of the values in the column: find the number of rows in the table and evaluate different prefix lengths until you achieve your desired uniqueness of rows.

FULLTEXT Indexes in MySQL

FULLTEXT indexes in MySQL are a different beast altogether. They have many limitations unique to themselves (for example, InnoDB has a stopword list comprised of 36 words while MyISAM stopword list is comprised of 143 words), they have unique search modes too. Some of them include a natural language mode (to activate such a search mode, run a FULLTEXT search query with no modifiers), you can also expand your search (to do that, use the WITH QUERY EXPANSION modifier – such a search mode performs the search twice, but when the search runs for the second time it includes a few most relevant records from the first search – frequently used when a user has implied knowledge of something), to search with boolean operators use the IN BOOLEAN MODE modifier. FULLTEXT indexes will also only be used if the search query consists of a minimum of three characters for InnoDB and a minimum of four characters for MyISAM.

Using B-Tree Indexes with Wildcards

Indexes are also frequently used if you’re building something similar to search engines. For that you frequently want to only search for a part of a value and return the results – here’s where wildcards step in. A simple query using a wildcard uses a LIKE query and the % sign to signify “anything” after the text. For example, a query like so would search for results beginning with the word “search” and having anything after it:

SELECT * FROM … WHERE demo_column LIKE ‘search%’;

A query like so would search for results beginning with anything, having the word “search” and having anything after it:

SELECT * FROM … WHERE demo_column LIKE ‘%search%’;

But here’s a catch – the above query will not use an index. Why? Because it has a wildcard at the beginning of itself and MySQL cannot figure out what the column needs to begin with. That’s why we said that wildcard indexes have their place, but only in specific scenarios – that is, such scenarios where you do not have a wildcard at the beginning of your search query.

Using ClusterControl to Monitor the Performance of Queries

Aside from using EXPLAIN, you can also use ClusterControl to monitor the performance of your queries: ClusterControl provides a set of advanced monitoring and reporting features that let you keep track of the performance of your database instances and queries. For example, click on a cluster and you will see a “Query Monitor” tab. Click on it and ClusterControl will let you observe the status of your queries in your database instances:

This part of ClusterControl lets you view a list of top slow and long-running queries while also allowing you to filter through them. For example if you know that not long ago you ran a query that consisted of @@log_bin, you can simply search for the term and ClusterControl will return a list of results:

As you probably noticed, you can also filter queries by hosts that you use or by occurrences, you can also elect to see a set of rows, for example, 20, 100 or 200. ClusterControl will also tell you when the query was last seen, what was its total execution time, how many rows it had returned, how many rows did it examine and so on. ClusterControl can prove to be instrumental if you want to observe how your indexes are actually used by MySQL, MariaDB, MongoDB, PostgreSQL or TimescaleDB instances.

Summary

In this blog post we went through some limitations and benefits concerning indexes in MySQL and we have also covered how ClusterControl can help you achieve your database performance goals. We will also have a third part about indexes in MySQL diving even deeper into them, but to conclude what we’ve covered so far, keep in mind that indexes in MySQL certainly do have their own place – to make the best of them, know how they interact with storage engines, their benefits and limitations, how and when to use certain types of indexes and choose wisely.