Why a MySQL Query Took 30 Seconds and How a Hidden Index Cut It to 0.2 s

1. Problem Background

In the live system a query on a table with about five million rows required more than 30 seconds; the query selects users under certain conditions using GROUP BY. In a test environment with the same data volume the query took 37 seconds.

The app_account column contains 5,000 distinct random values, each appearing roughly 1,000 times.

2. Execution Plan

The execution plan shows that the GROUP BY column has an index and that the index is being used.

3. Optimization Attempts

Several ideas were tried but did not noticeably improve performance:

Add ORDER BY NULL to avoid unnecessary sorting.

Add indexes to the fields used in the WHERE clause.

Reorder the WHERE conditions so indexed columns appear first.

Create composite indexes covering all WHERE fields.

Rewrite the query using sub‑queries.

Replacing GROUP BY with DISTINCT dramatically reduced the runtime on the test machine (0.8 s vs 37 s), but the same query on other computers still took more than 30 seconds.

4. Unexpected Behavior

It was later discovered that SQLyog automatically appends LIMIT 1000 to the query, which makes the query appear fast in that client. Running the same statement from the command line or on the server shows the real 30‑second execution time.

5. Final Solution

Forcing the use of the idx_end_time index (e.g., USE INDEX (idx_end_time)) reduced the query time to 0.19 seconds.

Before and after execution plans illustrate the improvement.