12 reasons why MySQL queries are slow

Tram Ho

1. SQL is not indexed

Many times our slow query is due to no index . If there is no index, it will cause a full table scan. Therefore, you should consider when querying columns with conditions, build indexes, and try to avoid full table scans.

The statement that begins with EXPLAIN in the image above is to get information about how MariaDB performs a SELECT, UPDATE, or DELETE. Looking at the type column you will see that it has a value of ALL . Meaning it is scanning the entire table which is not recommended for large tables. There are also some other types you can see in the following figure:

In the ideal case we would do the following:

Now look at the image above the type column has converted to the ref value. To know what the ref value means, you can see in the type section I shared above.

2. Index has no effect

Sometimes we explicitly add an index, but that index has no effect. So the index will not be valid in any cases?

2.1 Using query conditions other than data type

We create a users table as follows

The user_id field is a string type and is the global index of the B+ tree. If a number is used in the query condition the index will have no effect as shown below:

2.2 The query condition contains OR, which may cause the index to fail

We still use the same table structure as above

The user_id field is indexed, but age is not indexed. We use it in conjunction with OR and give the following result: For the OR query where age is not indexed, assume that it has already reached the index of user_id , but when it comes to the age try query condition, it must perform a full table scan, i.e. a three-step process is required. demand: full table scan + index scan + merge. If it scans the whole table at the beginning, it will be done with a single scan. Since for optimization in this case, the Mysql optimizer encounters OR conditions it won’t use the index, which seems reasonable.

Note : If all fields of the OR condition are indexed, the index can be used or not, you can try it yourself. But when using, you still have to pay attention to this condition and use explain analysis. When faced with a similar situation, consider separating the two SQLs.

2.3. As special characters can make the index invalid

Not that using like + special character index will be invalid, but similar query starting with % will cause index to fail. % reset at the end, the index is still running normally, specifically as follows:

2.4. The query condition does not meet the leftmost prefix of the common index

When MySQl creates a global index, it will follow the principle of leftmost prefix, i.e. the index will be used to search records based on the values ​​of the fields from left to right. If you create a common (a,b,c)index, it is equivalent to creating (a), (a,b), (a,b,c) three indexes. Suppose you have the following table structure:

There is a common index idx_userid_name, we execute this SQL, the query condition is invalid name index:

Because the query condition column name is not idx_userid_name the first column in the global index, the index is invalid. In a global index, the index is only valid when the query conditions satisfy the leftmost prefix.

2.5. Use MySQL’s built-in functions on indexed columns

Table structure:

Although the login_time index is added, due to using the built-in mysql function Date_ADD() , this index has no effect as shown in the figure: How to optimize this situation? The logic of built-in functions can be shifted to the right, as follows:

2.6 Performing operations with index columns (such as +, -, , /), index will have no effect

Table structure:

Although the age field is already indexed, due to the following writing, the direct index is lost. . . As shown in the picture:

Share the news now

Source : Viblo