Introduction

A simple random sample means every row in the population should have the same chance of being selected. In MySQL, the obvious approach is ORDER BY RAND() LIMIT n, which is correct for small and medium tables but can become expensive on large datasets because MySQL has to assign and sort random values across many rows.

The Straightforward Query

For correctness, the simplest query is still the clearest one:

1SELECT *
2FROM customers
3ORDER BY RAND()
4LIMIT 100;

This gives a simple random sample of 100 rows from the result set seen by the query.

It is easy to read and statistically reasonable for many practical cases. The downside is performance, not correctness.

Why ORDER BY RAND() Becomes Slow

RAND() is evaluated for each eligible row, then the rows must be sorted by those random values before the LIMIT is applied. On a large table, that means a lot of work just to return a small sample.

So the real engineering question is often:

• small table or ad hoc analysis: ORDER BY RAND() is fine
• large production table: consider alternatives

Filter First, Then Sample

If the sample should come from a subset, apply the filter first so the randomization happens over the intended population.

1SELECT *
2FROM customers
3WHERE country = 'CA'
4ORDER BY RAND()
5LIMIT 50;

This matters because “simple random sample” is only meaningful relative to the population you define in the query.

A Two-Step Pattern for Large Tables

When the table is large and has a reasonably dense numeric primary key, one practical approach is to select a random starting point and then fetch forward from there. That is fast, but it is only an approximation unless the id distribution and filtering conditions are carefully handled.

Example of the idea:

SELECT MAX(id) FROM customers;

Then from application code choose a random id threshold and query:

1SELECT *
2FROM customers
3WHERE id >= ?
4ORDER BY id
5LIMIT 100;

This can be useful operationally, but it is not the same guarantee as ORDER BY RAND(). If gaps or skew matter, be honest about the tradeoff.

Random Sampling Through a Derived Set

Another practical pattern is to first select candidate ids and then fetch the full rows.

1SELECT c.*
2FROM customers AS c
3JOIN (
4    SELECT id
5    FROM customers
6    ORDER BY RAND()
7    LIMIT 100
8) AS sample_ids
9ON c.id = sample_ids.id;

This is still based on RAND(), but it can be convenient when you want to separate the random selection logic from later joins or heavier row retrieval.

Sampling with Repeatability

If you need reproducible sampling for debugging or reports, document how the sample is generated. Pure random sampling without a stored seed gives a different result every run.

In some workflows, reproducibility matters more than fresh randomness. In that case, generate and store sample ids once or build the sample from a deterministic rule.

Simple Random Sampling Versus Fast Approximation

This distinction is important:

• 'ORDER BY RAND() gives a simple random sample from the query result.'
• id-range tricks and similar shortcuts are often approximations chosen for speed.

Both can be useful. They just solve different operational problems.

If the requirement is statistical validity, choose the method that preserves the sampling property. If the requirement is fast exploratory browsing, an approximation may be acceptable.

Sampling Outside SQL Is Also an Option

Sometimes the best design is to fetch only candidate keys from MySQL and perform the final sampling in application code. That is especially useful when the data already flows into a processing pipeline where sampling logic belongs closer to the analytics code than to the database.

But when you do that, the same rule still applies: the candidate set must represent the intended population fairly.

Common Pitfalls

• Assuming ORDER BY RAND() is always too slow without considering table size.
• Using an id-based shortcut and calling it a true simple random sample without qualification.
• Forgetting to apply filters before the randomization step.
• Sampling from the full table when the real population is a subset.
• Ignoring reproducibility when the sample must be explainable later.

Summary

• 'ORDER BY RAND() LIMIT n is the clearest correct MySQL pattern for simple random sampling.'
• Its main drawback is performance on large tables.
• Apply any population filter before sampling.
• Faster id-based approaches are often approximations, not exact simple random samples.
• Choose between exact sampling and speed based on the real requirement, not habit.