Slow SQL Statements Due to Improper Composite Index Settings

Scenario

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

Troubleshooting

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.
   Figure 1 CPU usage
   
2. Analyze slow query logs generated in that time 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 2 Slow query logs
   
3. Run SHOW INDEX FROM to check the index cardinalities of the three fields.
   Figure 3 Viewing 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. Therefore, 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 is caused by a performance bottleneck, such as 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.