The Query That Ate 75% of Our Database CPU: A MySQL Full-Text Post-Mortem

A news platform I maintain started serving pages in six seconds. Load average sat at 16 on a 12-core box for hours. Nothing had been deployed. Traffic was up, but not 10x up.

The cause turned out to be a single SELECT that looked completely reasonable — the kind of query that passes code review, works fine on a 5,000-row table, and quietly becomes a wrecking ball at 80,000 rows.

Here is the whole investigation: how I found it, why it was slow, what the fix was, and the three unrelated things I learned along the way.

Symptom first

The obvious metrics:

• Load average 16.7 on 12 cores
• MySQL at 92% CPU, resident memory 9.8 GB out of 15 GB
• 4.8 GB pushed into swap
• Time to first byte on article pages: 6.3 seconds

The tempting move here is to start tuning. Bump the buffer pool, add more cache, blame the bots. I've done that before and it's mostly a way of not finding the bug.

Find the query, not a query

The mistake I see most often at this stage is taking one snapshot of the process list, spotting something slow, and declaring victory. One snapshot tells you what was running at one instant. It doesn't tell you what dominates.

Sample it instead:

for i in $(seq 1 20); do
  mysql -N -e "SELECT info FROM information_schema.processlist
               WHERE command='Execute' AND info IS NOT NULL"
done | sed -E 's/.*MATCH.*/RELATED-ARTICLES/' | sort | uniq -c | sort -rn

Twenty cheap samples, bucketed by shape. The result:

    202 RELATED-ARTICLES
     41 other article queries
     21 rate-limit counter

202 of 268 active queries — 75% — were the same statement. That's not a slow query problem, that's a "one feature is eating the server" problem.

The query

It powers the "related articles" block under every story:

SELECT a.id, a.title, a.summary, a.image, a.published_at,
       MATCH(a.title, a.summary) AGAINST(? IN NATURAL LANGUAGE MODE) AS relevance
FROM articles a
LEFT JOIN categories c ON a.category_id = c.id
WHERE MATCH(a.title, a.summary) AGAINST(? IN NATURAL LANGUAGE MODE)
  AND a.id != ? AND a.status = '1'
ORDER BY (relevance / (DATEDIFF(NOW(), COALESCE(a.sort_date, a.published_at)) / 30 + 1)) DESC
LIMIT 6

Read that ORDER BY again. It divides the relevance score by the article's age in months, so a strong match from 2019 loses to a decent match from last week. It's a genuinely nice ranking idea. Freshness matters in news.

And the search term passed in? The current article's entire title plus the first 200 characters of its summary.

Why it's expensive

Two things compound.

Natural language mode is not a filter, it's a scorer. Feed it a long string and it matches on any meaningful token in that string. I measured it:

SELECT COUNT(*) FROM articles
WHERE MATCH(title, summary) AGAINST('<full title + 200 chars of summary>'
                                    IN NATURAL LANGUAGE MODE);
-- 16,141

16,141 rows matched out of 80,836 — about 20% of the table. A long natural-language term is a very wide net. Every extra common word widens it further.

The ORDER BY cannot use an index. It sorts on an expression computed per row. The optimizer has no choice:

+----------+----------------+----------------------------------------------+
| type     | key            | Extra                                        |
+----------+----------------+----------------------------------------------+
| fulltext | ft_title_summ  | Using where; Using temporary; Using filesort  |
+----------+----------------+----------------------------------------------+

Using temporary; Using filesort on a fulltext scan is the whole story. To return 6 rows, the server:

1. scores ~16,000 rows,
2. materialises them into a temporary table,
3. computes relevance / (age/30 + 1) for every one of them,
4. sorts all 16,000,
5. throws away 15,994.

Measured cost: ~0.22 s of CPU per call. On every article page view. With half the traffic coming from crawlers walking the archive, each hitting a different article — so per-article caching had a near-zero hit rate for exactly the traffic causing the load.

At a handful of article views per second, that one query alone wants more cores than the machine has.

Fix 1: stop searching with a paragraph

The search term should be the subject of the article, not the article. I extract meaningful words from the title only — drop stopwords, drop anything under three characters, cap at eight words:

function searchTermFromTitle(string $title): string
{
    $stop = ['and','the','with','for','from','that','this','was','are','has'];
    $clean = preg_replace('/[^\p{L}\p{N}\s]+/u', ' ', $title);
    $clean = trim(preg_replace('/\s+/u', ' ', $clean));

    $picked = [];
    foreach (explode(' ', $clean) as $word) {
        $lower = mb_strtolower($word, 'UTF-8');
        if (mb_strlen($lower, 'UTF-8') < 3 || in_array($lower, $stop, true)) {
            continue;
        }
        $picked[] = $word;
        if (count($picked) >= 8) break;
    }
    return $picked ? implode(' ', $picked) : $clean;
}

Match count dropped from ~16,000–58,000 (it varied wildly by article) to ~2,500–13,000. Better, not solved.

A note for non-English text: build the stopword list for the language you actually store. Mechanically reusing an English list on Turkish, German or Finnish content will either strip nothing or strip the wrong things, and morphology means a naive list misses inflected forms.

Fix 2: rank in two stages

The real problem isn't the match count, it's sorting all matches by an expression. So don't. Take the top N by raw relevance — which the fulltext index can drive with a bounded sort — then apply the freshness weighting to those N in an outer query:

SELECT t.id, t.title, t.summary, t.image, t.published_at, t.relevance
FROM (
    SELECT a.id, a.title, a.summary, a.image, a.published_at,
           MATCH(a.title, a.summary) AGAINST(? IN NATURAL LANGUAGE MODE) AS relevance,
           COALESCE(a.sort_date, a.published_at) AS rank_date
    FROM articles a
    LEFT JOIN categories c ON a.category_id = c.id
    WHERE MATCH(a.title, a.summary) AGAINST(? IN NATURAL LANGUAGE MODE)
      AND a.id != ? AND a.status = '1'
    ORDER BY relevance DESC
    LIMIT 60
) t
ORDER BY (t.relevance / (DATEDIFF(NOW(), t.rank_date) / 30 + 1)) DESC
LIMIT 6

The expensive sort now runs over 60 rows instead of 16,000. The derived table has a LIMIT, so it can't be merged away — it's materialised, which is precisely what I want here.

Ranking quality barely moves. A result that wins after freshness weighting was already a strong relevance match; it was never sitting at position 4,000.

Results

Measured against real rows, old query vs new, five articles:

	Before	After
5 queries, total	2.571 s	0.572 s
Share of active queries	75%	16%
Load average	16.7	7.4
TTFB, worst page	6.34 s	1.91 s

4.5x on the query. And no caching layer involved — this is the same work, arranged so the database isn't asked to sort a haystack to hand back six needles.

Three things I learned that weren't the bug

InnoDB does not give space back. I pruned about 2.9 million rows from some log tables. data_free went up by 270 MB and the files didn't shrink. Deleted rows leave free pages inside the tablespace, reusable by that table but not returned to the OS. OPTIMIZE TABLE rebuilds it. If your cleanup job only deletes, your disk usage will never reflect it.

A dynamic variable can fail silently. innodb_buffer_pool_size is dynamic on modern MariaDB, so I resized it live. No error. The value didn't change. The reason: innodb_buffer_pool_chunk_size read as 0, and resizing works in chunk units. Always read the variable back after you set it — don't trust the absence of an error.

Long-lived MySQL processes bloat. That instance had been up 85 days with a 1 GB buffer pool and was holding 9.8 GB resident, 4.8 GB of it swapped. After a restart with a properly sized pool: 1.96 GB resident, swap essentially empty, and 8 GB handed back to the OS. Per-connection buffers and temp-table churn accumulate; a restart is sometimes the honest fix.

What I'd take away

• Sample the process list, don't snapshot it. The dominant query and the slowest query are usually different queries, and the dominant one is what's hurting you.
• Using temporary; Using filesort next to a LIMIT 6 is a smell. It means the server built the whole set to return a fraction of it.
• Natural language full-text search scales with term length. A search term built from a whole paragraph is a query against a fifth of your table.
• Fix the shape before you add cache. Caching this would have hidden it from users while the crawler traffic — cache-missing by construction — kept the CPU pinned.

The bug had been there for a long time. It only became visible when the archive got big enough and the crawlers got busy enough for those two curves to cross.

---

I build news and e-commerce platforms in PHP at Alesta WEB, an independent software company running since 2005.