Indexing Your Data for High Performance

This chapter explains how to optimize MySQL performance by using indexes and query execution plans, and provides examples. Optimization involves a good understanding of the MySQL optimizer and index strategy and, finally, a solid knowledge of how indexes and queries work together.

In this chapter, we will cover the following topics:

• How does the MySQL optimizer think in MySQL 8.0?
• What kind of data type should I consider indexing first and why?
• Why should I have a good index strategy?
• What impact does an index have on MySQL performance?
• How to display and analyze a table structure
• How to efficiently read MySQL query execution plans
• How to know when to create an index
• Using multiple columns in an index
• Organizing columns in an index for best performance
• Case study 1 – how to use the explain plan tool in MySQL 8.0
• Case study 2 –...

How does MySQL optimizer think in MySQL 8.0?

Let's look at the main improvements in the MySQL 8.0 optimizer:

• MySQL 8.0 now more effectively supports advanced SQL features such as common table expressions, windowing functions, and the grouping() function
• One feature that DBAs will appreciate is the invisible index
• More tips in Query Rewrite Plugin
• On the performance side, the cost model has really been improved for superior performance
• Histogram statistics in MySQL 8.0 improve the statistics based on data distribution

I've always mentioned that to get optimal performance, you need to understand how MySQL thinks or reacts to your queries; this step in understanding is much more important than you think. For example, have you ever had situations where a complex query did not use the index that it had to use?

Let's have a look at how the high-level optimizer responds...

What kind of data type should I consider indexing first and why?

From a performance point of view, creating indexes is essential for system performance and future maintenance of your MySQL server database. Choosing the right indexes can give a significant boost to your application's performance.

In principle, the less space the column takes up, the faster your index will be. So, for example, what would be the column that would be the best candidate for a quick index, an INT or VARCHAR?

The answer would obviously be INT because the space used will be less than the VARCHAR. So, if performance is critical for you—and is probably why you're reading this book—I'll tell you that if you have the chance to have the smallest number possible, the better your index's performance will be. I'll give you an example: it is much better to create an index...

Why should I have a good index strategy?

Indexing your data is so broad that it's hard to know what to do and what not to do when developing your indexing strategies. You will agree with me that if you create indexes it is to improve the response time of your requests or applications, but indexing data is a balancing act.

Each index is a table that's managed by the system, so each addition or modification to the data in a table of your application potentially implies, as already mentioned in this book, the updating of these indexes, which can slow the performance of the updates to the data files.

The important points that you need to consider in your indexing strategy are as follows:

• Create a primary key (generally, the column will end with id)
• Predict the columns that will often be queried in your application with the WHERE, GROUP BY, HAVING, and ORDER BY clauses
• You...

What impact does an index have on MySQL performance?

Although adding indexes can help optimize the performance of your queries, adding indexes has a significant cost on MySQL performance.

In fact, when an index is added to a table, the performance of the writing is affected. Let me show you this from the music_album table. By examining the definition of the current table, you will notice a large number of indexes:

Table: music_album

By performing a simple benchmarking test, we can test the insertion rate of the current music album table with the original definition that included fewer indexes:

The following are the test results:

Did you notice that inserting data into the table with additional indexes was four times slower?

This test is quite basic and there are, of course, several other elements that can negatively affect insertion speed, but it provides a clear example of...

How to display and analyze a table structure

As we know, commands for SQL queries are both simple and easy to learn. Yet the efficiency of different queries and database functions can vary, and as the volume of information you are storing increases, this becomes more and more important. The same is true if your database supports a website, or if your site is experiencing heavier traffic.

In the context of optimizing your queries, it is very important to have an understanding at this stage regarding the display of the structure of a table in MySQL. Thanks to this display, you will be able to interpret from the explain plan, refer to it, and find a solution to your query performance problem.

When we display the structure of a table, this structure always tells me a little story about the following important points:

• How is the table used or will be used?
• The table will also tell...

How to efficiently read MySQL query execution plans

When you run a query, MySQL query optimizer tries to design an optimal execution plan for the query. You can see the plan information by mentioning EXPLAIN before your request.

EXPLAIN is one of the most powerful tools at your disposal to understand and optimize your MySQL queries that are slow or perhaps you have doubts regarding their performance, but unfortunately, many DBAs or developers rarely use them.

In this section, I will show you how to interpret the results of EXPLAIN and how to use it to optimize your queries.

How to effectively read the EXPLAIN results

The role of the EXPLAIN tool is to display the running cost before executing your DML queries; it's like...

How to know when to create an index

An index at the database level is the same principle as having an index at the end of a book. This is a completely separate section of the content from the rest of the book. So, do you see where I'm going with this? For example, if you are looking for a specific value, you can go to the index and search for it (the indexes are ordered, so finding things in this section is much faster than passing each page of the book).

A database index is an ordered list of relevant information that's used to expedite the search for records that match the criteria.

Specifically, you create an index when you have information that you need to look for frequently and need a quick answer for.

In other words, indexes prevent the DBMS from scanning the entire table looking for matching values, and they also help when you need to sort on a column.

Keep in...

Multiple column index versus multiple indexes

When discussing indexes, there is another question people often bring up: should we be using multiple columns in an index or multiple indexes on unique columns?

One mistake we often make is not taking the time to understand how indexing works and what we think is correct, indexing all columns used in queries separately.

In this case, you end up with a table that has multiple indexes. But these indexes end up having only one column. This kind of problem can be seen very quickly.

If you have queries with multiple columns in a WHERE clause, you will probably need multiple indexes that will contain multiple columns for optimal performance. But wait, we must keep in mind that indexing all possible combinations, thinking that you will get better performance, may very well have an opposite effect.

Let's use an example of indexes with...

How to organize your columns in an index for good performance

In this section, we will specifically cover the order of the columns in a multi-column index or COMPOUND type. The order in which you will put your columns will allow you to have queries that perform. Now, in what order should we put them? It depends on how you are going to query your table with your queries. An index can be used to perform an exact search or filtering with the WHERE clause.

A specific search is when the values of all columns in the index are specified and the query uses exactly the same order.

We can say that this type of index is defined as follows:

index( col_A, col_B, col_C )

The advantage of a COMPOUND index is beneficial for a query if it uses these columns in joins, filtering, and sometimes in a particular selection. There will be other benefits for queries that use the left-most subset of columns...

Tips and techniques

In this section, I will share the five most popular tips and we will review a real case of a company that is facing a performance problem with a query in MySQL.

The five general rules for your indexes

The five general rules to know when it is appropriate to create an index would be to respond positively to the following questions:

• Can you Index each primary key?
• Can you Index each foreign or secondary key?
• Can you Index each column used in a JOIN clause?
• Can you Index each column used in a WHERE?
• Can you Index each column used in a GROUP BY clause?
• Can you Index each column used in an ORDER BY clause?
• Are you going to generate reports?

In other words, indexes become necessary when you plan to use joins...

Summary

What is very important in the field of optimization is to understand how the MySQL optimizer thinks when using the tools that are available, such as EXPLAIN.

In addition to understanding and familiarizing yourself with the MySQL EXPLAIN tool, you must apply the steps we explained in organizing your columns in the order in which you use the WHERE, GROUP BY, and ORDER BY commands.

We also shared the new MySQL 8.0 features for EXPLAIN and other components.

In the next chapter, we will show you advanced performance techniques for the large queries that you can use in your projects.