How Do I Handle Slow SQL Statements Caused by Inappropriate Composite Index Settings?

Scenario

On your instance, an SQL query that ran at 11:00 and was expected to take 8 seconds took more than 30 seconds.

Possible Causes

1. Check the CPU usage. In this example, during that time period, the CPU usage of the instance did not increase sharply and remained low, so we know that the slow query was not caused by high CPU usage.
2. Analyze slow query logs generated during that period. In this example, shown below, there were several SQL statements that involved millions of rows being scanned. These were the slow statements. But no large amount of data was inserted into the table during that time, so we know that the slow execution was caused by missing or incorrect index settings. By running EXPLAIN, you can find that the execution plan of the SQL statement was full table scanning.
   Figure 1 Slow query logs
   
3. Perform SHOW INDEX FROM on the table on the instance to check the cardinality of the three columns.
   Figure 2 Index cardinality
   

   The query_date field with the smallest cardinality was in the first place of the composite index, and the group_id field with the largest cardinality was in the last place of the composite index. In addition, the SQL statement contained the range query of the query_date field. As a result, only the query_date field was indexed.

   The SQL statement could only use the index of the query_date column. Additionally, the optimizer may have selected full table scanning during cost estimation because the cardinality was too small.

   A new composite index was created with the group_id field in the first place and the query_date field in the last place. The query time met the expectation.

Solution

1. Check whether the slow query was caused by insufficient CPU resources.
2. Check whether the table structure is properly designed and whether index settings are correct.
3. Execute the ANALYZE TABLE statement periodically to prevent incorrect execution plans because performing a large number of INSERT or DELETE operations for table data may result in outdated statistics.