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.