MySQL Slow Query Optimization: Reducing Execution Time from 30 Seconds to 0.19 Seconds

The author encountered a severe slow‑query problem in production: a SELECT with GROUP BY on a table containing five million rows took more than 30 seconds, even though an index existed on the GROUP BY column.

Execution‑plan screenshots show that the index on the GROUP BY column was being used, yet the query remained sluggish.

Several optimization ideas were tried without success: adding ORDER BY NULL to suppress unnecessary sorting, creating composite indexes for the complex WHERE clause, and even replacing GROUP BY with DISTINCT, which unexpectedly reduced the runtime dramatically in test runs.

Further investigation revealed a puzzling discrepancy: the same SQL executed via the SQLyog client finished in under a second, while command‑line or other clients still took 30+ seconds. The cause was identified as SQLyog automatically appending LIMIT 1000 to the query, making it appear fast.

After discarding the client‑side shortcut, the author forced MySQL to use the idx_end_time index. This change reduced the execution time to about 0.19 seconds, confirming that the previous index choices (e.g., idx_org_id , idx_mvno_id ) were suboptimal for this query.

Final execution‑plan comparisons show the dramatic improvement, and the author notes that the issue was resolved by explicitly specifying the appropriate index.