Practical MySQL Slow Query Optimization: Case Studies and Techniques

After reading a blog from the Ele.me team about Spring‑RabbitMQ pitfalls, the author was inspired to write a technical article with an eye‑catching title and solid content, focusing on practical MySQL slow‑query optimization in production.

1. Slow SQL #1

Problem discovery

When the application was deployed to production, the front‑end request to the back‑end API took at least 6 seconds. The slow SQL observed was:

Reproducing the slow SQL

Execute the following SQL:

<ol>
<li><p><code>select count(*) from sync_block_data</code></p></li>
<li><p><code>where unix_timestamp(sync_dt) >= 1539101010</code></p></li>
<li><p><code>and unix_timestamp(sync_dt) <= 1539705810</code></p></li>
</ol>

The execution time was 2658 ms. The execution plan (EXPLAIN) showed a type of index, indicating an index scan.

Optimization of slow query #1

The sync_dt column is of datetime type. By rewriting the query to compare dates directly instead of converting them to timestamps, the SQL becomes:

<ol>
<li><p><code>select count(*) from sync_block_data</code></p></li>
<li><p><code>where sync_dt >= "2018-10-10 00:03:30"</code></p></li>
<li><p><code>and sync_dt <= "2018-10-17 00:03:30"</code></p></li>
</ol>

The query now takes 419 ms (about six times faster). The new execution plan shows a type of range.

Optimization of slow query #2

The original query counted data for the last seven days using unix_timestamp in the WHERE clause. By removing the upper bound comparison, the SQL becomes:

<ol>
<li><p><code>select count(*) from sync_block_data where sync_dt >= "2018-10-10 00:03:30"</code></p></li>
</ol>

The execution time dropped to 275 ms, halving the previous duration.

Optimization of slow query #3

A new bigint column syncdtlong was added to store the millisecond value of sync_dt, and an index was created on it. The query on the copy table runs in 34 ms, and after further tuning it runs in 22 ms.

<ol>
<li><p><code>select count(*) from copy_sync_block_data where sync_dt_long >= 1539148502916</code></p></li>
</ol>

In production, the overall request time fell to about 900 ms, and after adding a 10‑second cache the average response time became ~20 ms.

Explanation of EXPLAIN fields

The article then explains the meaning of each column in the MySQL EXPLAIN output (id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra) and emphasizes that the type column has the greatest impact on performance, with the order from best to worst being: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL. Queries should aim for at least range, preferably ref.

Causes of slow queries

Using functions on indexed columns in the WHERE clause (e.g., unix_timestamp) forces a full table scan because the index cannot be used.

Solutions

Avoid function calls on indexed columns; compute the needed values in application code and pass them as constants.

Postscript

The issue was not observed in the test environment because the data volume was only tens of thousands, whereas production holds millions of rows, amplifying the performance problem.

Source: Juejin article, compiled by the Souyunku Technical Team.