A significant production incident recent occurred involving a database table containing over 80 million records. During peak traffic, slow query logs spiked to 140,000 requests per minute, causing connection exhaustion and system-wide latency. The offending SQL statement followed this pattern:

SELECT 
  * 
FROM 
  user_activity_logs 
WHERE 
  region_id = 42 
  AND category = 12 
ORDER BY 
  id DESC 
LIMIT 1;

Despite the simplicity of the query, execution times reached 40 seconds. Examination of the execution plan revealed that the MySQL optimizer ignored valid composite indexes in favor of the primary key.

Root Cause Identifictaion

The table structure included several indexes intended to optimize this type of lookup:

-- Existing indexes
KEY `idx_region_cat` (`region_id`, `category`),
KEY `idx_composite_extended` (`region_id`, `category`, `created_at`)

When executing EXPLAIN on the query, the output showed the following behavior:

• possible_keys: idx_region_cat
• key: PRIMARY
• rows: 2150
• Extra: Using where

MySQL's optimizer chose the PRIMARY index because the query includde an ORDER BY id DESC with a LIMIT 1. The optimizer's cost model assumed that since the primary key is already sorted, it could scan the table backwards and find the first matching row faster than using a composite index and then sorting the results in memory.

However, in this specific case, the combination of region_id = 42 and category = 12 yielded zero results for a large portion of the data set. This forced MySQL to perform a massive scan of the primary key, iterating through millions of rows only to find nothing, resulting in severe performance degradation.

Index Selection Logic and Sampling

The optimizer estimates the number of rows (cardinality) using statistics gathered via data page sampling. InnoDB samples $N$ random pages to estimate the distribution of values. If the data distribution is uneven or the sampling is outdated, the estimated rows value in the EXPLAIN output can be significantly lower than the actual effort required.

In this scenario, the optimizer underestimated the cost of the primary key scan and overestimated the cost of using idx_region_cat due to the perceived overhead of filesort.

Mitigation Strategies

Several methods can be employed to rectify this behavior and ensure the optimizer selects the most efficient path.

1. Index Hinting

The most immediate fix is to force the optimizer to use the correct index, bypassing its internal cost calculation.

SELECT 
  * 
FROM 
  user_activity_logs FORCE INDEX(idx_region_cat)
WHERE 
  region_id = 42 
  AND category = 12 
ORDER BY 
  id DESC 
LIMIT 1;

This approach reduces execution time to milliseconds but introduces maintenance overhead, as index renames or deletions in the future would break the application logic.

2. Composite Index with Sorting Column

A more robust solution is to include the sorting column in the composite index. By adding id to the end of the existing index, the optimizer realizes that the composite index can provide the data in the required order without a separate sorting step.

CREATE INDEX idx_region_cat_id ON user_activity_logs (region_id, category, id);

With this index, the optimizer sees a perfectly ordered set for the filter criteria, making it the clear choice over a full primary key scan.

3. Adjusting the LIMIT Threshold

Increasing the LIMIT value can sometimes change the optimizer's cost preference. If the LIMIT is large enough, the cost of scanning the primary key outweighs the cost of using a composite index plus a filesort. However, this is often unreliable and acts more as a side-effect than a deliberate optimization.

4. Subquery Optimization

Wrapping the filtering logic in a subquery can force the evaluation order. By isolating the filtered ID search, you can ensure the composite index is utilized before the final limit is applied.

SELECT 
  * 
FROM 
  user_activity_logs 
WHERE 
  id = (
    SELECT id 
    FROM user_activity_logs 
    WHERE region_id = 42 AND category = 12 
    ORDER BY id DESC 
    LIMIT 1
  );