MySQL Performance Boosting with Indexes and Explain

Key Points

• Use MySQL's EXPLAIN command to analyze and optimize query execution plans, ensuring more efficient database operations by revealing key information such as connection type and index usage.
• Implement query analysis to measure the actual running time of a query, thereby performing targeted optimizations to reduce execution time and improve overall performance.
• Add appropriate indexes based on feedback from the EXPLAIN command, focusing on the columns used in the WHERE clause to speed up data retrieval and improve query performance.
• For columns involved in search operations, consider using full-text indexing to optimize performance, especially when using the LIKE operator in queries.
• Note the use of ORDER BY in combination with LIMIT, as it may offset the performance advantages of limiting results, especially if the index is not used effectively.

Database optimization is usually the primary focus in improving application performance and the most common bottleneck. How to measure and understand what needs improvement?

A simple and effective tool is query analysis. Enabling analysis allows more accurate estimates of the run time of a query. This is a two-step process: first, enable analysis; then, call show profiles to get the query run time.

Suppose the following insertion operation exists in the database (and assuming that user 1 and gallery 1 have been created):

<code class="language-sql">INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');</code>

A small amount of data will not cause problems, but we can use it for simple analysis. Consider the following query:

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

If there are many photo entries, this query may become a problem in the future.

To get the exact run time of this query, you can use the following SQL:

<code class="language-sql">set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;</code>

The results are as follows:

Query_Id	Duration	Query
1	0.00016950	SHOW WARNINGS
2	0.00039200	SELECT * FROM homestead.images AS i WHERE i.description LIKE '%street%' LIMIT 0, 1000
3	0.00037600	SHOW KEYS FROM homestead.images
4	0.00034625	SHOW DATABASES LIKE 'homestead'
5	0.00027600	SHOW TABLES FROM homestead LIKE 'images'
6	0.00024950	SELECT * FROM homestead.images WHERE 0=1
7	0.00104300	SHOW FULL COLUMNS FROM homestead.images LIKE 'id'

The

show profiles; command not only displays the time of the original query, but also the time of all other queries, so that the query can be analyzed accurately.

How to improve query?

You can rely on SQL knowledge to improve, or rely on MySQL's EXPLAIN command and improve query performance based on actual information.

EXPLAIN is used to get the query execution plan, that is, how MySQL executes the query. It is suitable for SELECT, DELETE, INSERT, REPLACE, and UPDATE statements and displays information about the statement execution plan by the optimizer. The official documentation describes well how EXPLAIN how

can help us:

EXPLAINWith EXPLAIN, you can see which tables you should add indexes so that statements can execute faster by using indexes to find rows. You can also use

to check if the optimizer joins the tables in the best order.

EXPLAINTo give an example to illustrate the use of UserManager.php, we will use the query to find user emails in

:

<code class="language-sql">INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');</code>

EXPLAINTo use the SELECT command, just add it before the

type query:

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

The result is as follows (scroll right to see everything):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	const	UNIQ_1483A5E9E7927C74	UNIQ_1483A5E9E7927C74	182	const	1	100.00	NULL

These results are not easy to understand at the beginning, let's take a closer look at each one:

• id: This is the sequential identifier for each query in SELECT.
• select_type: SELECT type of query. This field can take multiple different values, so we will focus on the most important ones:
  • SIMPLE: Simple query without subqueries or unions
  • PRIMARY: select Located in the outermost query in the connection
  • DERIVED: select is part of the neutron query from
  • : The first in the subquerySUBQUERYselect
  • : UNION is the second or subsequent statement of the union. The complete list of select field values ​​can be found here. select_type
• : The table referenced by rows. table
• : This field represents the type of table used by MySQL connection. This is probably the most important field in the type output. It can indicate the missing index, or it can show how the query is overridden. Possible values ​​for this field are as follows (sorted from best to worst type): EXPLAIN
  • : The table has zero rows or one row. system
  • : There is only one row in the table that matches the row, and the row has been indexed. This is the fastest connection type. const
  • : All parts of the index are used by the join, and the index is eq_ref or PRIMARY_KEY. UNIQUE NOT NULL
  • : For each row combination from the previous table, all matching rows of the index column are read. This type of join usually occurs in indexed columns compared using ref or operators. =
  • : Join the full text index of the table. fulltext
  • : The same as ref_or_null, but also contains the rows from the column's ref value. NULL
  • : Connections use index lists to generate result sets. The index_merge column of EXPLAIN will contain the keys used. KEY
  • : unique_subquery Subquery returns only one result from the table and uses the primary key. IN
  • : Use indexes to find matching rows within a specific range. range
  • : Scan the entire index tree to find matching rows. index
  • : Scan the entire table to find the matching rows for the join. This is the worst type of join, usually indicating a missing appropriate index on the table. ALL
• : Shows the keys that MySQL can use to find rows from a table. These keys may or may not be used in practice. possible_keys
• : Indicates the index that MySQL actually uses. MySQL always finds the best keys that can be used for queries. When joining multiple tables, it may find some keys that are not listed in keys but are better. possible_keys
• : Indicates the length of the index to be used by the query optimizer. key_len
• : Displays a column or constant that compares to the named index in the ref column.
• rows: Lists the number of records checked to generate output. This is a very important indicator; the fewer records checked, the better.
• Extra: Contains other information. The Using filesort or Using temporary equivalent in this column may indicate a query in question.

EXPLAINThe complete documentation of the output format can be found on the official MySQL page.

Back to our simple query: it is a SIMPLE type select with a connection of const type. This is the best query case we may have. But what happens when we need bigger and more complex queries?

Back to our application mode, we may want to get all the gallery images. We may also want to include only photos with the word "cat" in the description. This is definitely a situation we can find in project requirements. Let's look at the query:

<code class="language-sql">INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');</code>

In this more complex situation, we should get more information in EXPLAIN to analyze:

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

This will give the following results (scroll right to see all cells):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	index	PRIMARY,UNIQ_1483A5E9BF396750	UNIQ_1483A5E9BF396750	108	NULL	1	100.00	Using index
1	SIMPLE	gal	NULL	ref	PRIMARY,UNIQ_F70E6EB7BF396750,IDX_F70E6EB7A76ED395	UNIQ_1483A5E9BF396750	108	homestead.users.id	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

Let's take a closer look and see what we can improve in the query.

As mentioned earlier, the main columns that should be viewed first are the type columns and rows columns. The goal should be to get better values ​​in the type column and minimize the values ​​of the rows column.

The result of the first query is index, which is not a good result at all. This means we may be able to improve it.

View our query, there are two ways to solve it. First, the Users table is not used. We either extend the query to make sure we are targeting the user or we should delete the user portion of the query completely. It only increases the complexity and time of our overall performance.

<code class="language-sql">INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');</code>

So now we get the exact same result. Let's see EXPLAIN:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	PRIMARY,UNIQ_1483A5E9BF396750	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

What we are left with is the ALL type. While ALL is probably the worst type of connection, there are some cases where it is the only option. According to our requirements, we want all the gallery images, so we need to search the entire galleries table. When we need all the information in the table, indexes are great when trying to find specific information in the table, but they don't help us. When we encounter this situation, we have to resort to other methods, such as caching.

Since we are working on LIKE, the last improvement we can make is adding a full text index to our description field. This way, we can change LIKE to match() and improve performance. More information about full-text indexing can be found here.

We also have to check out two very interesting situations: the latest and related features in the application. These apply to gallery and involve some extreme situations that we should pay attention to:

<code class="language-sql">INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');</code>

The above are related galleries.

<code class="language-sql">SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';</code>

The above is the latest gallery.

At first glance, these queries should be very fast because they use LIMIT. This is the case in most queries that use LIMIT. Unfortunately for us and our applications, these queries also use ORDER BY. Because we need to sort all results before limiting the query, we lose the advantage of using LIMIT.

Since we know ORDER BY can be tricky, let's apply our reliable EXPLAIN.

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	IDX_F70E6EB7A76ED395	NULL	NULL	NULL	1	100.00	Using where; Using filesort
1	SIMPLE	u	NULL	eq_ref	PRIMARY,UNIQ_1483A5E9BF396750	PRIMARY	108	homestead.gal.id	1	100.00	NULL

and,

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort

We can see that for both of our queries, we have the worst connection type: ALL.

Historically, MySQL's implementation, especially when used with ORDER BY, was often the source of MySQL performance issues. This combination is also used in most interactive applications with large data sets. Features like new registered users and popular tags often use this combination. LIMIT

Because this is a common problem, we should also apply some common solutions to solve performance problems.

Make sure we are using indexes. In our case,
• is a good candidate because it is the field we are sorting. This way we can do created_at and ORDER BY without scanning and sorting the complete result set. LIMIT
Sort by columns in the leading table. Generally, if
• is sorted by fields that are not the first table in the join order, the index cannot be used. ORDER BY
Do not sort by expression. Expressions and functions do not allow
• to use indexes. ORDER BY
Pay attention to the large value of
• . Large LIMIT values ​​will force LIMIT to sort more rows. This will affect performance. ORDER BY

When we use both

and LIMIT, these are some of the measures we should take to minimize performance issues. ORDER BY

Conclusion

As we have seen,

is very useful for identifying problems in queries as early as possible. There are many problems that are only noticed when our application is in production and there are a lot of data or a lot of visitors to access the database. If you can use EXPLAIN to detect these problems as early as possible, then the possibility of performance problems in the future is much smaller. EXPLAIN

Our application has all the indexes needed and is fast, but we now know that whenever we need to check for performance gains, we can always turn to

and indexes. EXPLAIN

FAQs about MySQL Performance Indexing (FAQ)

What is the importance of MySQL performance indexing?

MySQL performance indexing is crucial to optimize database performance. They significantly speed up data retrieval operations by quickly accessing rows in the data table based on the values ​​in the indexed columns. Without an index, MySQL will have to traverse every row in the table to find the relevant rows, which can be very time-consuming, especially for large databases.

How does the EXPLAIN command help improve MySQL performance?

The

command in MySQL is a powerful tool that provides information about how MySQL performs queries. It shows the order of read tables, the type of read operations performed, the index that can be selected, and the estimated number of rows to be checked. This information can help developers optimize queries and improve database performance. EXPLAIN

Why does MySQL not use any possible keys?

MySQL does not use any possible keys for several reasons. One reason may be that the optimizer estimates that using indexes requires scanning most of the table and decides that table scanning will be faster. Another reason might be that the columns in the WHERE clause do not match the columns in the index.

How to optimize my MySQL query?

There are several ways to optimize MySQL queries. One way is to use indexes effectively. Indexes can significantly speed up data retrieval. However, they slow down data modification operations such as INSERT, UPDATE and DELETE. Therefore, it is very important to find a balance point. Another way is to use the EXPLAIN command to understand how MySQL executes queries and finds potential bottlenecks.

What is the difference between primary keys and indexes in MySQL?

The primary key in MySQL is an index. The primary key is a unique identifier for the row in the table. It enforces the uniqueness of a column or column combination and ensures that the column or column combination does not contain NULL values. On the other hand, an index is a data structure that can increase the speed of data retrieval operations. It can be applied to any column or combination of columns.

How to create an index in MySQL?

You can use the CREATE INDEX statement to create an index in MySQL. The syntax is as follows: CREATE INDEX index_name ON table_name (column1, column2, …);. This creates an index on the specified column of the specified table.

What is the composite index in MySQL?

Composite index, also known as multi-column index, is an index containing multiple columns. In MySQL, a composite index can contain up to 16 columns, but the total size of the indexed columns cannot exceed 767 bytes.

How to delete index in MySQL?

You can use the DROP INDEX statement to delete the index in MySQL. The syntax is as follows: DROP INDEX index_name ON table_name;. This will delete the specified index from the specified table.

What is the difference between clustered index and nonclustered index in MySQL?

Clustered index determines the physical order of data in the table. Each table can only have one clustered index. On the other hand, nonclustered indexes do not change the physical order of data in the table. Instead, it maintains a separate data structure (index) pointing to the data row, allowing for faster data retrieval.

How to select the index to use in MySQL?

MySQL uses a cost-based optimizer to select the index to use. The optimizer estimates the cost of executing plans for different queries and selects the lowest-cost plan. Cost is estimated based on factors such as the number of rows to be read, the number of disk lookups, CPU cost, and memory usage.

The above is the detailed content of MySQL Performance Boosting with Indexes and Explain. For more information, please follow other related articles on the PHP Chinese website!