The table already had an index on the right columns, yet a "related posts" SQL query still hung for tens of seconds, the processlist filled with stacked-up queries, and the database server's load jumped to three digits. This is a typical case of MySQL 8.4 picking the wrong execution plan on skewed data. This article walks through the fix one attempt at a time, each attempt teaching a lesson: ORDER BY column choice, the optimizer's driving table, the limits of FORCE INDEX, and a seemingly harmless <> condition. The final solution splits the query into two simple steps, bringing the time down from several seconds to milliseconds.

Symptoms

  • A database server with little RAM, no swap, and high max_connections and table_open_cache values.
  • Many connections from minions sitting in the executing state for several seconds; under heavier traffic, some queries ran for minutes.
  • Load average spiking and CPU maxed out whenever a few people opened an article detail page at the same time.

Investigating the query on MySQL 8.4

Step 1: find the running query

All investigation work on a MySQL 8.4 server should go through a read-only account:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 200)
FROM information_schema.processlist
WHERE COMMAND NOT IN ('Sleep', 'Daemon') AND ID != CONNECTION_ID()
ORDER BY TIME DESC;

The query that showed up most often:

SELECT posts.id, posts.name, posts.image, posts.description, posts.slug
FROM posts
INNER JOIN post_category_maps ON posts.id = post_category_maps.post_id
WHERE post_category_maps.post_category_id = ?
  AND posts.status = 1
  AND posts.id <> ?
ORDER BY posts.id DESC
LIMIT 5;

It is the "related posts" block at the bottom of every detail page: fetch the 5 newest posts in the same category, excluding the post being viewed.

Step 2: the index exists, so why is MySQL 8.4 still slow?

SHOW CREATE TABLE shows no missing index: posts has PRIMARY (id) and idx_status; the mapping table has idx_category_post (post_category_id, post_id). But EXPLAIN reports Using temporary; Using filesort over every post in the category: MySQL fetches them all, joins them to posts, dumps them into a temporary table and only then sorts to get 5 rows. The cause: ORDER BY posts.id sorts by a column of the other table, so the optimizer cannot walk the (post_category_id, post_id) index backward and stop early.

-- fix: sort by the mapping table's column
ORDER BY post_category_maps.post_id DESC
-- EXPLAIN: Backward index scan; Using index

The two columns are equal through the join condition, so the result is identical. On a small category, the fixed query is hundreds of times faster. It is easy to think the job is done.

Step 3: the first fix makes large categories slower

When one category holds most of the posts, the MySQL 8.4 optimizer compares the row counts of the two tables and picks posts as the driving table, scans the whole table, and again creates a temporary table and a filesort. The query becomes slower than the original. A fix that was right for small data turned out wrong for large data, because it was only measured on a small category.

Step 4: FORCE INDEX on MySQL 8.4 is not enough

DB::table(DB::raw('post_category_maps FORCE INDEX (idx_category_post)'))
    ->join('posts', 'posts.id', '=', 'post_category_maps.post_id')
    ->where('post_category_maps.post_category_id', $category->id)
    ->where('posts.status', 1)
    ->where('posts.id', '<>', $post->id)
    ->orderBy('post_category_maps.post_id', 'desc')
    ->limit(5)

A test on the large category showed a clear speedup, along with a MySQL restart to clear the hung queries and the accumulated heap. But CPU still spiked whenever a few requests arrived at once. We needed to know exactly what the plan was doing.

Step 5: EXPLAIN ANALYZE exposes a range scan caused by <>

EXPLAIN only gives estimates. MySQL 8.4's EXPLAIN ANALYZE actually executes the statement and prints the real row counts, loop counts and timings at each node:

EXPLAIN ANALYZE
SELECT posts.id, ...
FROM post_category_maps FORCE INDEX (idx_category_post)
INNER JOIN posts ON posts.id = post_category_maps.post_id
WHERE post_category_maps.post_category_id = ?
  AND posts.status = 1
  AND posts.id <> ?
ORDER BY post_category_maps.post_id DESC LIMIT 5;

-> Nested loop inner join  (actual time=... rows=5 loops=1)
    -> Filter: (posts.status = 1)
        -> Index range scan on posts using PRIMARY
           over (id < X) OR (X < id)   (rows ≈ almost the whole table)
    -> Single-row index lookup on post_category_maps
           using idx_category_post     (loops ≈ almost the whole table)

This is the crux. For an indexed column, the optimizer may turn id <> X into two ranges, id < X and id > X, as described in the MySQL 8.4 Range Optimization documentation. Those two ranges cover almost the whole table, but in the optimizer's eyes, posts now has a "range" on the primary key and becomes attractive as the driving table. FORCE INDEX only forces the index for the mapping table; it does not force the join order. The result: a scan of nearly all of posts, then a lookup back into the mapping table for every row, costing several seconds of CPU per request.

This is not a MySQL 8.4 bug: the optimizer does exactly what it is allowed to do; its cost estimate is simply wrong for this data distribution.

Step 6: split it into two steps so MySQL 8.4 has nothing to choose

// Step 1: a few of the newest ids in the category, read straight from idx_category_post
$postIds = DB::table('post_category_maps')
    ->where('post_category_id', $category->id)
    ->where('post_id', '<>', $post->id)
    ->orderBy('post_id', 'desc')
    ->limit(10)
    ->pluck('post_id');

// Step 2: fetch posts by primary key, keeping the Eloquent model
$related_posts = Post::select('id', 'name', 'image', 'description', 'slug')
    ->whereIn('id', $postIds)
    ->where('status', 1)
    ->orderBy('id', 'desc')
    ->limit(5)
    ->get();

The <> condition is still there, but it now applies to the mapping table, within a single category of the composite index, so it drags no large table along. Each step reads only a handful of rows, the total time is under a millisecond, and it no longer depends on category size.

A common mistake after optimizing: changing the return type

If step 2 is written with DB::table('posts'), the result is stdClass instead of a Post model. Any view that calls a model accessor or method then returns a 500 error. Query optimization in Laravel must keep the data type the view expects.

Root cause

  1. A JOIN of two large tables with ORDER BY on the other table's column prevents MySQL from using the index to stop early.
  2. For a large category, the MySQL 8.4 optimizer picks the wrong driving table. The posts.id <> X condition becomes a range scan on the primary key, making the bad plan look cheaper than it really is.
  3. Caching "related posts" per article is not enough: on a site with many articles, every view of an article not yet cached still runs the heavy query once.
  4. A small-RAM server with no swap and high max_connections: when queries pile up, RAM and CPU have no room to breathe.

Fixing slow queries on MySQL 8.4

  • Temporary: kill the hung queries or restart MySQL 8.4 to free connections and the accumulated heap.
  • Long-term: split the query into two steps as above, keeping the Eloquent model in step 2.
  • Trade-off to be aware of: step 1 takes 10 ids and only then filters on status = 1, so if many of the newest posts are hidden, the block may show fewer than 5 posts. Raise the number of ids in step 1 if needed.
  • Server configuration, following the process in Reducing MySQL RAM on a small VPS: buffer and connection tuning: set max_connections and table_open_cache to real needs, and add swap as a safety net.

Prevention

  • Every new or modified query on MySQL 8.4 must be run through EXPLAIN ANALYZE on the largest data set it will face, not just the first example.
  • Be careful with the pattern "JOIN of two large tables + <> on the primary key + ORDER BY on the other table + a small LIMIT". Prefer fetching ids from the index first and records by primary key afterward.
  • FORCE INDEX is a narrow hint: it does not force the join order. Do not treat it as the final answer.
  • Enable the slow query log with a reasonable threshold on shared MySQL 8.4 and 8.0 servers, so slow queries show up before load spikes.
  • After every return-type change (Eloquent to query builder), open the real page before calling it done.

For problems with locks rather than query plans, see MySQL deadlock analysis: reading gap locks and insert intention in InnoDB status.

Lessons

  • Having an index is not enough. Sort order, the driving table and filter conditions can all make MySQL 8.4 skip the best index.
  • Measure the worst case. Data distribution determines the plan. A fix that is much faster on a small category can be much slower on a large one.
  • EXPLAIN guesses, EXPLAIN ANALYZE counts. Only the real loop counts reveal why FORCE INDEX was not enough.
  • Simple queries are more predictable than clever ones. Two queries on an index and a primary key give the MySQL 8.4 optimizer no chance to choose wrong.

Next article

100% CPU on MySQL: waiting locks and missing indexes. A sales application maxes out database CPU every morning, and how to test an index directly on production without letting the schema drift from the code.