Why Skipping Secondary Indexes Can Speed Up MySQL Queries: A Real‑World Example

Phenomenon

A client reported a SQL query taking 35 seconds, and the execution plan showed no index usage.

Analysis

The WHERE clause columns were examined for selectivity. Except for start_time, other columns had low selectivity. The table holds about 4.8 million rows (~2 GB).

mysql> select min(start_time),max(start_time) from job_history;
+---------------------+---------------------+
| min(start_time)     | max(start_time)     |
+---------------------+---------------------+
| 2023-12-29 02:36:28 | 2024-01-19 06:44:01 |
+---------------------+---------------------+
1 row in set (0.02 sec)

mysql> show table status like 'job_history'\G
*************************** 1. row ***************************
Name: job_history
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 4819722
Avg_row_length: 376
Data_length: 1816133632
Index_length: 1232748544
Data_free: 108003328
Auto_increment: 4961289
Create_time: 2024-01-23 17:20:22
Collation: utf8mb4_bin

The optimizer ignored indexes because their selectivity was low, making a full table scan faster.

Optimization

Since id and start_time are both monotonically increasing, the condition start_time > '2024-01-17 02:36:28' can be rewritten to use the primary key:

start_time > '2024-01-17 02:36:28'

Equivalent rewritten condition:

id >= (select max(id) from job_history where start_time <= '2024-01-17 02:36:28')

Testing the rewritten query showed the execution time reduced to 2.55 seconds.

Conclusion

When the optimizer has accurate statistics, it usually picks the optimal execution path, and manual hints can degrade performance. However, if the optimizer lacks precise information, knowledgeable users can improve performance by leveraging application‑specific knowledge—such as the fact that id and start_time grow sequentially—to rewrite queries for primary‑key access, achieving significant speedups.