Introduction

ORDER BY RAND() is convenient and often fine for tiny tables, but it becomes expensive as the table grows because MySQL has to assign a random value across a large candidate set and then sort it. For frequently used random-selection queries, that quickly becomes a performance bottleneck. Better alternatives depend on how random the result needs to be and whether you can afford some preprocessing.

Why ORDER BY RAND() Gets Expensive

The classic query is easy to write.

1SELECT id, title
2FROM articles
3ORDER BY RAND()
4LIMIT 10;

The problem is not just generating random numbers. The expensive part is sorting a large number of rows after assigning those random values.

That is why you should start by inspecting the execution plan.

EXPLAIN SELECT id, title FROM articles ORDER BY RAND() LIMIT 10;

On large tables, you will often see full-scan or filesort behavior that does not scale well.

Strategy 1: Random Offset Sampling

For moderate workloads, a random offset is often the simplest replacement.

First get the row count.

SELECT COUNT(*) AS total FROM articles;

Then choose a random offset in application code and run a plain LIMIT query.

SELECT id, title
FROM articles
LIMIT 12345, 10;

This avoids sorting the whole table randomly, but it is not perfect sampling in every scenario. Filters, sparse visibility, or skewed conditions can reduce the quality of randomness.

Still, it is often good enough when the real goal is “random-looking results fast enough for users”.

Strategy 2: Persistent Random Key Column

If you need frequent random selection with better scaling, a persistent random key column is a strong approach.

ALTER TABLE articles ADD COLUMN random_key DOUBLE NOT NULL;
UPDATE articles SET random_key = RAND();
CREATE INDEX idx_articles_random_key ON articles(random_key);

Then query from a random threshold.

1SET @r = RAND();
2SELECT id, title
3FROM articles
4WHERE random_key >= @r
5ORDER BY random_key
6LIMIT 10;

If the query reaches the upper end of the random key space and returns too few rows, run a second query from the low end and combine the results. This pattern scales much better than random sorting at request time.

Strategy 3: Random Primary-Key Jump

If primary keys are dense enough, you can jump near a random ID and read forward.

1SELECT id, title
2FROM articles
3WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM articles))
4ORDER BY id
5LIMIT 10;

This is fast because it uses indexed access, but it becomes biased when IDs contain many gaps from deletions or archival patterns. So it is best when the key space is reasonably dense.

Strategy 4: Precomputed Random Pools

For very hot endpoints, precomputing a random candidate pool can move the expensive work out of the request path.

1CREATE TABLE article_random_pool (
2  slot_id INT PRIMARY KEY,
3  article_id BIGINT NOT NULL,
4  updated_at DATETIME NOT NULL
5);

A background job refreshes the pool periodically. The user-facing query then becomes a cheap lookup or join against that pool. This is often the best approach when latency requirements are strict and the dataset is large.

Filters Change the Right Answer

Random selection is often combined with constraints such as status, category, or region. That means the real query is not random across the whole table. It is random inside a filtered subset.

In those cases, index design matters just as much as the sampling strategy.

CREATE INDEX idx_articles_status_random ON articles(status, random_key);

A fast random strategy that ignores business filters is usually not useful in practice.

Benchmark the Distribution and the Latency

Two questions matter:

• is the selection random enough for the product requirement,
• and is the query fast enough under load.

That means you should measure both statistical quality and operational performance. The best optimization is not always the mathematically purest sample. It is the one that meets the product’s actual randomness and latency targets.

Common Pitfalls

• Replacing ORDER BY RAND() without checking whether the new approach is random enough for the business case.
• Ignoring filters and optimizing only the unfiltered query shape.
• Using random primary-key jumps on sparse or gap-heavy ID spaces.
• Adding a random key column without indexing it.
• Debating sampling strategies without measuring actual latency and plan behavior.

Summary

• 'ORDER BY RAND() is simple but scales poorly on large tables.'
• Random offset sampling is easy and often good enough for moderate workloads.
• Indexed random-key columns are a strong choice for frequent random reads.
• Primary-key jumps can be fast but become biased on sparse key spaces.
• Pick the strategy based on both randomness quality and request-path performance.