Why MySQL Ignored My Index and How to Speed Up Periodic Deletion Queries

Introduction

When regularly deleting rows older than a certain date, the following DELETE statement was used:

delete from testtable where biz_date <= '2017-08-21 00:00:00' and status = 2 limit 500;

A composite index idx_bizdate_st (biz_date, status) had already been added, yet the query remained extremely slow.

Analysis

Running EXPLAIN on the query revealed 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 estimated about 980 k rows, almost the whole table (≈1 M rows), and therefore chose a full scan.

Force Index Attempt

Using a hint to force the index:

select * from testtable force index(idx_bizdate_st) 
where biz_date <= '2017-08-21 00:00:00' and status = 2;

Returned an empty set quickly, but the execution time was still far from acceptable because the index range remained huge.

Narrowing the Date Range

Since the business logic deletes only one day at a time, the query was rewritten to restrict the date range explicitly:

select * from testtable 
where biz_date >= '2017-08-20 00:00:00' 
  and biz_date <= '2017-08-21 00:00:00' 
  and status = 2;

The new EXPLAIN output shows a range scan with dramatically fewer rows:

+----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-------------------+
| id | select_type | table     | type  | possible_keys  | key            | key_len | ref  | rows   | Extra             |
+----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-------------------+
| 1  | SIMPLE      | testtable | range | idx_bizdate_st| idx_bizdate_st | 6       | NULL | 789    | Using index cond |
+----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-------------------+

Rows examined dropped from ~980 k to 789, and the query executed in virtually no time.

Conclusion

Forcing the index alone did not solve the performance problem; the real gain came from aligning the SQL with the business requirement and limiting the scan range. Understanding the data distribution and writing queries that let MySQL use the index efficiently is the most effective optimization strategy.