Why MySQL Ignored My Index and How to Speed Up Periodic Deletions

Background

In a routine cleanup task a developer needs to delete rows older than a certain date from testtable where status = 2. The initial statement is:

mysql> delete from testtable WHERE biz_date <= '2017-08-21 00:00:00' AND status = 2 limit 500\G

A composite index idx_bizdate_st(biz_date, status) has already been created on the table, which is about 200 MB in size.

Problem

Despite the index, the query runs extremely slowly. An EXPLAIN without the LIMIT shows a full‑table scan:

+----+-------------+-----------+------+----------------+------+---------+------+--------+-------+
| id | select_type | table     | type | possible_keys  | key  | key_len | ref | rows   | Extra |
+----+-------------+-----------+------+----------------+------+---------+------+--------+-------+
| 1  | SIMPLE      | testtable | ALL  | idx_bizdate_st | NULL | NULL    | NULL| 980626 | Using where |
+----+-------------+-----------+------+----------------+------+---------+------+--------+-------+

The optimizer estimates about 980 k rows (≈ 98 % of the table) for the condition biz_date <= … AND status = 2, so it decides a full scan is cheaper than using the index.

Investigation

Further checks reveal that the actual row count for the combined condition is zero, while the table contains roughly 990 k rows in total. The statistics therefore mislead the optimizer. Re‑collecting statistics does not change the estimate, suggesting MySQL may only use the first column of the composite index for cardinality estimation.

Attempts to Fix

Forcing the index with FORCE INDEX(idx_bizdate_st) makes the query use the index, but the execution time remains unsatisfactory because the index still covers a huge range.

mysql> SELECT * FROM testtable FORCE INDEX(idx_bizdate_st) 
       WHERE biz_date <= '2017-08-21 00:00:00' AND status = 2;

The result set is empty, yet the query still scans many index entries.

Solution: Narrow the Date Range

Since the cleanup deletes only one day at a time, the query can be rewritten to restrict the scan to a single day:

mysql> SELECT * FROM testtable 
       WHERE biz_date >= '2017-08-20 00:00:00' 
         AND biz_date <= '2017-08-21 00:00:00' 
         AND status = 2;

Running EXPLAIN on this statement shows a range access on the composite index with only a few rows examined (e.g., 5–789 rows), and the query finishes instantly.

Conclusion

Forcing an index is not enough when the optimizer estimates a massive range; the real fix is to rewrite the SQL so that the index can limit the scan to a small interval that matches the business logic. Understanding the data distribution and aligning the query with it is essential for a DBA to achieve optimal performance.