A shuffle table picks a random WordPress post 14,000x faster than WordPress 7.2’s ORDER BY RAND()

WordPress 7.2 adds a random content feature. Visit /random/ and you get redirected to a random post. Visit /random/category/news/ and you get a random post from that category, and adding ?random to a search does the same for search results.

It’s a nice little feature. The way it picks the post is ORDER BY RAND(), and anyone who has run a large WooCommerce store will have just winced a bit. So I measured it.

On a store with 1,000,000 products, one hit on the random URL took 3,372 ms of database time. A shuffle table does the same job in 0.24 ms.

What the feature does

WP::handle_random() runs the normal main query for whatever archive you asked for, with three extra callbacks attached:

• wp_random_content_pre_get_posts() limits the query to published posts without a password, from post types registered as randomable
• wp_random_content_posts_orderby() replaces the ORDER BY with RAND()
• wp_random_content_post_limits() replaces the LIMIT with LIMIT 0, 1

WordPress then sends a 302 redirect to that post’s permalink, with nocache_headers() and X-Robots-Tag: noindex, follow.

Using the main query is a deliberate choice, and a good one for flexibility. Every pre_get_posts filter a site already has (excluded categories, membership rules, whatever) applies to the random pick automatically.

Two defaults are worth knowing:

1. It’s on by default. The filter wp_enable_random_content_redirect defaults to true.
2. randomable defaults to publicly_queryable && has_archive. WooCommerce products have an archive (the shop), so products are eligible unless the post type says otherwise.

To turn it off:

add_filter( 'wp_enable_random_content_redirect', '__return_false' );

The query

I generated this through a real WP_Query with the same settings core applies:

SELECT wp_posts.ID
FROM wp_posts
WHERE 1=1
  AND wp_posts.post_password = ''
  AND wp_posts.post_type = 'product'
  AND ((wp_posts.post_status = 'publish'))
ORDER BY RAND()
LIMIT 0, 1

Core already selects only the ID here and fetches the post afterwards (a change made on 30 October 2026 for exactly this reason: a SELECT * would copy whole posts into the temporary table). That helps, but it doesn’t touch the actual problem.

Why ORDER BY RAND() is slow

RAND() has no index. The database cannot know which row has the lowest random number until it has generated a random number for every row. So for LIMIT 1 it still has to:

1. Read every eligible row (1,000,000 of them here)
2. Look up the full row for each one, because post_password isn’t in any index
3. Write them all to a temporary table (on disk here, since 1,000,000 rows doesn’t fit in MariaDB’s default 16MB tmp_table_size)
4. Sort that table and return the first row

Here’s what MariaDB reported:

Strictly speaking it isn’t a full table scan, since it does use an index to find the products. But when products make up nearly the whole of wp_posts, that index range is nearly the whole table, so it costs about the same.

The cost grows with the number of eligible posts, not with the LIMIT. On a blog with 500 posts you’ll never notice. On a store with 1,000,000 products, every single hit costs 3.4 seconds of database time.

Caching doesn’t help either. The response is deliberately uncached (so you get a different post each time), and it’s an anonymous GET request. Page caches and CDNs pass every request straight through to PHP and the database. That makes /random/ the cheapest way I know to make a big WordPress site’s database work hard, and it ships turned on.

A category is cheaper because the category narrows the rows first. My test category has 83,134 products, and the same query took 182 ms. That’s still a full read and sort of every product in the category, on every hit.

The shuffle table

The expensive part of ORDER BY RAND() is shuffling everything on every request. So shuffle once a day instead, store the result, and have each request jump straight to a random spot in it.

Build it once a day

CREATE TABLE wp_random_new (
  post_type VARCHAR(20)     NOT NULL,
  random_id INT UNSIGNED    NOT NULL,
  post_id   BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (post_type, random_id)
);

SET @i := 0;
INSERT INTO wp_random_new (post_type, random_id, post_id)
SELECT post_type, (@i := @i + 1), ID
FROM (
  SELECT post_type, ID FROM wp_posts
  WHERE post_status = 'publish' AND post_password = '' AND post_type = 'product'
  ORDER BY RAND()
) s;

RENAME TABLE wp_random TO wp_random_old, wp_random_new TO wp_random;
DROP TABLE wp_random_old;

Every eligible post gets a number from 1 to N in shuffled order, with no gaps. Building and swapping it took 3.5 to 4.3 seconds for 1,000,000 products. That’s about the cost of one hit on core’s /random/ today, paid once a day from cron instead of on every request.

The RENAME TABLE swap is atomic, so a request sees either yesterday’s shuffle or today’s, never half of each. And it never writes to wp_posts.

(Why not a random_id column on wp_posts? I tried that too. Renumbering 1,000,000 rows of wp_posts in place took 3 to 5 minutes as one transaction holding row locks on every product, and on a WooCommerce store that’s where your stock updates live. It also needs a schema change to wp_posts, which core avoids for good reason.)

Pick a post on each request

Pick a random number between 1 and N in PHP, then ask for the first post at or above it:

SELECT s.post_id
FROM wp_random s
JOIN wp_posts p ON p.ID = s.post_id
WHERE s.post_type = 'product'
  AND s.random_id >= 734512
  AND p.post_status = 'publish' AND p.post_password = ''
ORDER BY s.random_id
LIMIT 1

That’s a seek on the primary key: the database goes straight to 734,512 in the B-tree and reads one row. The join back to wp_posts re-checks that the post is still published. Because the numbers have no gaps, every post has the same chance of being picked.

0.24 ms median, against core’s 3,372 ms.

Note that it’s >= and not LIMIT 734512, 1. An OFFSET doesn’t jump to a row. It reads and throws away every row before it, which on average is half the table.

Categories

For a category, add the join to wp_term_relationships:

JOIN wp_term_relationships tr
  ON tr.object_id = p.ID AND tr.term_taxonomy_id = 46

The database walks forward from the random point until it finds a post in the category. With 83,134 of 1,000,000 products in this category, that’s about 12 rows on average (2 in the run I analysed). 0.41 ms median, against core’s 182 ms.

The loose ends

• Wrap-around. If nothing is found at or above the random number, run the query again from 1.
• New posts. Posts published since the last shuffle aren’t in the table yet. Append them on publish with MAX(random_id) + 1, or accept a day’s delay.
• Unpublished posts. The join skips them, which gives the next post in the shuffle two chances until the next rebuild.
• Categories aren’t perfectly uniform. A post’s chance depends on how many non-category posts sit before it in the shuffle. That’s random, and it’s re-rolled every day, which is fine for a “surprise me” link.
• Arbitrary filters. This covers post types and taxonomy archives. Core’s version also honours any pre_get_posts filter and search results, which a shuffle table can’t do on its own.

Simpler options

If a new table is more than you want, there are two simpler fixes. Both are worse than the shuffle table, but either is far better than what core does now.

Pick from the newest N posts. Limit the archive’s normal date-ordered query to 1,000 rows, then ORDER BY RAND() over just those. 1.97 ms median for the whole catalogue. It’s still the main query, so every pre_get_posts filter still applies, but only the newest 1,000 posts can ever be picked. For the category it took 208 ms, about the same as loading page one of that category, so it doesn’t help categories on this dataset.

Cache a pool of IDs. Run core’s ORDER BY RAND() query once an hour with LIMIT 100, store the IDs in a transient or the object cache, and pick one per request with array_rand(). That means no random query per request at all. The refill took 3,847 ms, paid once an hour instead of on every hit. You need one pool per archive.

Results

All on the same 1,000,000-product dataset, 20 runs each with a new random number per run.

Category with 83,134 products:

Scheduled costs:

Summary

• ORDER BY RAND() LIMIT 1 reads and sorts every eligible post, so its cost grows with the size of the site. On 1,000,000 products that’s 3.4 seconds per hit.
• The random redirect can’t be cached and needs no login, so every hit reaches the database. It’s on by default in 7.2, and WooCommerce products are eligible by default.
• A shuffle table, rebuilt once a day in about 4 seconds, picks a random product in 0.24 ms, about 14,000 times faster. For a category it’s 0.41 ms, about 440 times faster.
• Seek into it with >=. Don’t use OFFSET, because it reads every row it skips.

If you run a large store and don’t want this feature yet, the one-line wp_enable_random_content_redirect filter above turns it off.

How I measured this

• 1,000,000 published WooCommerce products with about 10 million term relationships, imported with Super Speedy Imports from our simple-products-1m dataset, then copied into a throwaway database for these tests
• MariaDB 10.11.14, InnoDB buffer pool 8GB, tmp_table_size 16MB (the default), query cache off
• AMD Ryzen 9 5900X, 12 cores, 40GB RAM, WSL2
• Core’s query was generated by a real WP_Query using the settings from WordPress trunk on 6 November 2026. My local WordPress is 7.1, so I attached the three random callbacks myself rather than running 7.2
• Timings come from MariaDB’s SHOW PROFILES. Each approach had one warm-up run and then 20 timed runs, with a new random number each time
• I haven’t tested MySQL 8, which may choose different query plans

Test this on your own million products

The dataset is free. Download simple-products-1m.csv (311 MB), with its import config, from the Super Speedy Imports demo data repository.

In our published benchmark, Super Speedy Imports imported this million products, with no image downloads, in about 22 minutes on a 4-vCPU server.

You can import it with the free Super Speedy Imports Lite from WordPress.org. Then you can test your own plugins, theme and queries against a million products instead of finding out on a live store. The WordPress.org edition imports with a single worker from WP-CLI, so expect that route to take longer (87 minutes for the same file in the same benchmark).