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

Background: In a production environment a query on a table with 5 million rows took more than 30 seconds, prompting the need for SQL optimization.

Test Setup: The author generated 5 million rows in a test environment, with the app_account field distributed across 5,000 distinct values, each appearing about 1,000 times.

Initial Observation: The simple GROUP BY query took 37 seconds to complete.

Execution Plan: The plan showed that the indexed GROUP BY field was being used.

Optimization Attempts:

Idea 1 – Adding ORDER BY NULL to avoid unnecessary sorting (no noticeable improvement).

Idea 2 – Adding composite indexes for all WHERE fields (still slow).

Idea 3 – Replacing GROUP BY with DISTINCT, which unexpectedly reduced the runtime dramatically.

Unexpected Behavior: After testing on multiple machines, the same SQL executed in 0.8 seconds on the author's computer but still took over 30 seconds on other machines and the server.

Root Cause Discovery: The discrepancy was due to the client tool SQLyog automatically appending LIMIT 1000 to the query, making it appear fast, while command‑line and other tools executed the full query.

Final Solution: By explicitly forcing the use of the idx_end_time index in the query, execution time dropped to 0.19 seconds.

Before and After Execution Plans (images retained from the original article):

After applying the index fix, the query completes in a few hundred milliseconds (image shown).

Conclusion: The issue was not a MySQL bug but a client‑side limitation; forcing the appropriate index resolved the performance problem.