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

Scenario

On a TaurusDB instance, a 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.
   Figure 1 CPU usage
   
2. Analysis of the slow query logs generated during that time period shows that when the SQL statement ran fast, the number of scanned rows was in the millions, but when it ran slowly, the scanned rows jumped to tens of millions. After confirming with the business team that no large amount of data was inserted into the table in the short term, it is inferred that the slow execution was caused by no index being used or an incorrect index being selected. By running EXPLAIN, you can find that the execution plan of the SQL statement was full table scanning.
   Figure 2 Slow query logs
   
3. Perform SHOW INDEX FROM on the table on the instance to check the cardinality of the three columns.
   Figure 3 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. The remaining two fields were out of order and could not be 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.