An internal query page against a table of barely 2,000 rows took 3–4 seconds per click. In December 2016, we dug into the slow query log for the first time over slowness where 'the data volume is trivially small.' Notes on what we found — all beginner-level traps.
1. The Slow Log Was On, but the Threshold Was the Default 10 Seconds
long_query_time defaults to 10, so 3–4-second queries never got logged. We set GLOBAL long_query_time = 1, turned on slow_query_log, reproduced once, and finally saw the statements. This step ate half a day, because at first we assumed logging wasn't actually enabled and restarted mysqld twice for nothing.
2. Pagination Used OFFSET 1900
The list page ran LIMIT 20 OFFSET 1900 — MySQL scans the first 1,900 rows before returning anything. Even 2,000 rows means scanning 1,900 and discarding them; the last page is the slowest. We switched to id-range pagination keyed off the previous page's max id — details another day.
3. A Function Wrapped the Time Column
The condition was WHERE DATE(create_time) = '2016-12-01'. create_time had an index, but wrapping it in DATE() made the index unusable — full table scan. Changed to create_time >= '2016-12-01 00:00:00' AND create_time < '2016-12-02 00:00:00': same results, index used. EXPLAIN showed it: type went from ALL to range, rows from 2,000+ down to about 30.
4. Mismatched Join Column Types
The main table's user_id was INT; another table's user_id was VARCHAR(20). The JOIN forced an implicit conversion, killing the index again. We unified both sides to INT — 3 tables altered — and added two composite indexes while we were in there.
5. SELECT * Dragged Back Two TEXT Fields
The list page shows 5 columns, but the query was SELECT *, pulling a remark field and an attachment-path TEXT field along for the ride — heavier network transfer and heavier temp tables. Changed to naming those 5 columns explicitly.
The Result
After the five fixes, the page went from 3–4 seconds to near-instant. No cache added, no hardware swapped — just a proper, honest walk through EXPLAIN.
We've since put EXPLAIN into our code review checklist. Small data volume doesn't mean fast — that's the lesson from that day.
