MySQL Slow Query Optimization: Live Demonstration on a Production To-Do List

In a production environment the to‑do list query took 5–6 seconds, prompting an urgent investigation.

Step 1: Analyze the SQL, which joins several tables and includes a large IN clause on flow_id , causing the execution time to jump from 80 ms to 5.8 s.

<code>***from event i </code><code> left join project p on i.project_id = p.project_code </code><code> left join dict d on i.type_id = d.id </code><code> left join record re on re.incident_id = i.id</code><code> left join type it on it.id = i.type_id </code><code> where i.version_flag = 0 and i.flow_id in (大量条件)***</code>

Step 2: Check indexes with EXPLAIN; the columns re.incident_id and i.flow_id were not using indexes, and the record table performed a full table scan.

Step 3: Verify that the character sets of the joined columns match; the event table used utf8mb4 while record used utf8 . Aligning both to utf8mb4 reduced the query time to under one second.

<code>DEFAULT CHARSET=utf8mb4</code>

<code>DEFAULT CHARSET=utf8</code>

Step 4: Force index usage when the index cardinality is too low, e.g., LEFT JOIN alarm_order2 ao FORCE INDEX(alarm_id) ON a.id = ao.alarm_id .

<code>left join alarm_order2 ao force index(alarm_id) on a.id=ao.alarm_id</code>

Step 5: Understand IN clause behavior – it uses indexes only when the matching rows are less than about 30 % of the table; otherwise it triggers a full scan, so large IN lists can be rewritten as LEFT JOINs.

The article concludes with a reminder to share and bookmark the post.