How to Install the Random Record Query Function

Updated at 1784648445000

When you need to fetch a random list of records from a database table, the most common approach is usually ORDER BY RAND() or ORDER BY RANDOM(). This is easy to write, but it can be expensive on large tables because the database has to generate random values for many rows and sort them.

A lighter approach is to randomize the starting position, then fetch a small page using LIMIT.

Idea

Instead of randomizing every row, we can do it in 3 steps:

Count the total number of matching records.
Pick a random skip value within a valid range.
Query records using OFFSET skip LIMIT limit.

flowchart TD
    A[Receive query conditions] --> B[Count total matching records]
    B --> C{total > limit?}
    C -->|No| D[skip = 0]
    C -->|Yes| E[skip = random from 0 to total - limit]
    D --> F[Query with OFFSET and LIMIT]
    E --> F
    F --> G[Return random records]

Implementing random skip limit

You can create a reusable helper function:

public Next randomSkipLimit(long total, long limit) {
    long skip = 0;
    if (limit < total) {
        skip = ThreadLocalRandom
            .current()
            .nextLong(0, total - limit + 1);
    }
    return Next.skipLimit(skip, limit);
}

Where:

total is the total number of records matching the condition.
limit is the number of records you want to fetch.
skip is the randomly selected starting position.
If total <= limit, the function returns skip = 0.

Query example

Suppose we have a post table and want to fetch random published posts:

SELECT COUNT(*)
FROM post
WHERE status = 'PUBLISHED';

After getting total, we generate a random skip and run:

SELECT *
FROM post
WHERE status = 'PUBLISHED'
LIMIT :limit OFFSET :skip;

In Java, the service may look like this:

public List<Post> randomPublishedPosts(int limit) {
    long total = postRepository.countByStatus("PUBLISHED");

    Next next = randomSkipLimit(total, limit);

    return postRepository.findByStatus(
        "PUBLISHED",
        next
    );
}

Repository example

public interface PostRepository {

    long countByStatus(String status);

    List<Post> findByStatus(
        String status,
        Next next
    );
}

If you use plain SQL, Next can be replaced with offset and limit:

public RandomPage randomPage(long total, int limit) {
    long offset = 0;
    if (limit < total) {
        offset = ThreadLocalRandom
            .current()
            .nextLong(0, total - limit + 1);
    }
    return new RandomPage(offset, limit);
}

public record RandomPage(long offset, int limit) {}

Why not use ORDER BY RAND

For small tables, this approach is still fine:

SELECT *
FROM post
WHERE status = 'PUBLISHED'
ORDER BY RAND()
LIMIT 5;

But on large tables, the database often has to generate and sort random values for many rows before returning only 5 records. This can slow down queries, especially when the API is called frequently.

Using a random offset is often lighter because the database only needs to:

count matching records,
jump to an offset,
fetch a small number of rows.

Things to keep in mind

This approach works well when you need a reasonably random group of records, such as:

recommended posts,
random products,
random banners,
featured items that change on each page load.

If you need a highly accurate random distribution for every individual record, or if the table changes very frequently, consider using caching, ID snapshots, or a primary-key-based random strategy.

Conclusion

A random record query can be implemented neatly with this formula:

skip = random(0, total - limit)

Then query with:

LIMIT :limit OFFSET :skip

This avoids ORDER BY RAND() on large tables, is easy to package as a reusable helper, and is efficient enough for many random-content features in web applications.

Previous Page Deployment Guide for Infrastructure Providers Using EzyPlatform