RAND() #

ORDER BY RAND() LIMIT 10 is one of the queries developers write most often when they need random data — and also one of the most common causes of production performance problems. The query looks simple, works right away, and produces correct output. The problem is hidden in how the database executes it: to get 10 random rows, the database must read, shuffle, and sort the entire table first. On a table with millions of rows, this single query can drain the database CPU and cause a domino effect across the whole system. This article covers why this happens, four far more efficient alternatives, and how to handle similar patterns like ORDER BY COUNT which shares the same root problem.

How ORDER BY RAND() Works Internally #

To understand why ORDER BY RAND() is expensive, you first need to understand what the database actually does when executing this query. It’s not just “fetching random rows” — the process is far heavier than that.

The Hidden Execution Steps #

The ORDER BY RAND() LIMIT 10 execution process:
──────────────────────────────────────────────────────────────
  Table: articles (1,000,000 rows)

  Step 1: Full Table Scan
    → Read ALL 1 million rows from disk into memory
    → No index can help — RAND() is non-deterministic

  Step 2: Generate a Random Value per Row
    → Call RAND() 1 million times
    → Each row gets a float value between 0 and 1
    → Example: row 1 = 0.7234, row 2 = 0.1892, etc.

  Step 3: Full Sort
    → Sort 1 million rows by the random values
    → Sort algorithm: O(n log n)
    → If the data doesn't fit in memory → spill to disk (filesort)

  Step 4: Take the Top 10 Rows
    → After sorting, discard 999,990 rows
    → Return 10 rows

  Total: read 1 million rows, sort 1 million rows, discard 999,990 rows.
  Just to get 10 rows.
──────────────────────────────────────────────────────────────

Why Indexes Can’t Help #

Indexes work because their values are deterministic and stored — the database knows that the email value of a certain column is [email protected], and that value doesn’t change between query executions. Indexes can be used to jump directly to the right row.

RAND() produces a different value every time it’s called, per row, per execution. There’s no way to store random values in an index because the values don’t exist until the query runs. The result:

-- EXPLAIN for ORDER BY RAND()
EXPLAIN SELECT * FROM articles ORDER BY RAND() LIMIT 10;

-- Output:
-- +------+-------------+----------+------+------+-----------------------------+
-- | type | key         | rows     | ref  | Extra                        |
-- +------+-------------+----------+------+------+-----------------------------+
-- | ALL  | NULL        | 1000000  | NULL | Using temporary; Using filesort |
-- +------+-------------+----------+------+------+-----------------------------+

-- type = ALL       → full table scan
-- key = NULL       → no index used
-- Using temporary  → MySQL creates a temporary table
-- Using filesort   → sorting done outside the index

Two red flags at once: Using temporary and Using filesort. This is the most expensive combination that can appear in the Extra column.

The Production Impact #

The impact of ORDER BY RAND() on a live system isn’t just a slow query — it creates a domino effect:

The ORDER BY RAND() domino effect in production:
──────────────────────────────────────────────────────────────
  Query executed
      │
      ▼
  Database reads 1 million rows → high disk I/O
      │
      ▼
  In-memory sort → large database memory usage
      │
      ├─ If memory isn't enough → spill to disk
      │       → disk I/O gets even higher
      │       → latency spikes
      │
      ▼
  CPU spike (sorting is a CPU-intensive operation)
      │
      ▼
  Other queries queue up → whole system latency rises
      │
      ▼
  Connection pool fills up → requests time out
      │
      ▼
  Error 500 on the user side
──────────────────────────────────────────────────────────────
  One "small" query can bring the whole system to this state
  if executed by many concurrent users at once.

This case is very common: the team sees the database CPU at 95%, everyone panics looking for the cause, and after several hours of investigation finds one endpoint calling ORDER BY RAND() — and that endpoint is called every time the homepage loads.

Four Scalable Alternatives #

Each alternative below has different trade-offs. Choose based on the need: must the randomness be perfect? Is the data very large? Is a little bias in the distribution acceptable?

Alternative 1: ID Sampling with MAX(id) #

This approach leverages the fact that an integer primary key is indexed, so the database can jump directly to a specific row without scanning.

-- ANTI-PATTERN: the expensive ORDER BY RAND()
SELECT * FROM articles ORDER BY RAND() LIMIT 1;
-- → Full scan of 1 million rows, sort 1 million rows

-- CORRECT: ID sampling with MAX(id)
SELECT *
FROM articles
WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM articles))
ORDER BY id
LIMIT 1;
-- → Subquery: 1 lookup for MAX(id) via the index
-- → WHERE id >= X: range scan starting from X, take 1 row
-- → No sort, no full scan

To take several rows at once with more even distribution:

-- Taking 10 random rows with several anchor points
SELECT *
FROM articles
WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM articles))
ORDER BY id
LIMIT 10;

-- Note: this takes 10 SEQUENTIAL rows starting from a random point,
-- not 10 independently random rows. For certain use cases
-- this is sufficient; for others a different approach is needed.

The limitation of this approach: if many IDs have been deleted (non-continuous IDs), the distribution is uneven — areas with many deleted IDs will rarely be selected.

When ID Sampling fits:
  ✓ Numeric auto-increment IDs
  ✓ Rare data deletion (relatively continuous IDs)
  ✓ Need 1 random item, not many at once
  ✓ Random distribution doesn't have to be perfect

When it doesn't fit:
  ✗ UUID/string IDs
  ✗ Many deleted rows (gappy IDs)
  ✗ Need truly even distribution

Alternative 2: Precomputed Random Value (a `rand_value` Column) #

This is the most scalable approach for large tables with consistent random-content needs. The idea is simple: store a random value in an indexed column, then query using that column.

-- Add a random column to the table
ALTER TABLE articles
ADD COLUMN rand_value FLOAT NOT NULL DEFAULT 0,
ADD INDEX idx_articles_rand_value (rand_value);

-- Fill the random value on insert
INSERT INTO articles (title, content, rand_value)
VALUES ('Article Title', 'Content...', RAND());

-- Or mass-update existing data
UPDATE articles SET rand_value = RAND();

-- The very fast random query
SELECT * FROM articles
ORDER BY rand_value
LIMIT 10;
-- → type: index, key: idx_articles_rand_value
-- → Index scan only, no need to read every row
-- → No runtime sort — already ordered in the index

To make the random display feel “fresh” each time, add a periodic refresh mechanism:

-- Refresh random values periodically (e.g. via a cron job)
-- Don't update everything at once in production — it can lock the table
UPDATE articles
SET rand_value = RAND()
WHERE id BETWEEN ? AND ?;  -- batch by ID range

-- Or trigger a refresh when new content is added
-- by re-randomizing part of the old data
UPDATE articles
SET rand_value = RAND()
ORDER BY rand_value  -- update ones that have been "selected" for a while
LIMIT 1000;

When Precomputed Random Value fits:
  ✓ Large tables (> 100 thousand rows)
  ✓ Random content accessed frequently (homepage, widgets)
  ✓ Repetition across different sessions is acceptable
  ✓ A mechanism exists to refresh values periodically

When it doesn't fit:
  ✗ Need truly fresh randomness on every request
  ✗ Tables rarely accessed (the column overhead isn't worth it)

Alternative 3: Shuffle in the Application Layer #

This approach moves the randomization logic from the database to the application. The database only needs to return a list of IDs — light work leveraging indexes — then the application shuffles and picks.

// Example implementation in Go

// Step 1: get all IDs from the database (uses the index → fast)
rows, err := db.Query("SELECT id FROM articles WHERE status = 'published'")
// → Only fetches the ID column, not all columns
// → Leverages the index on the status + id columns

var ids []int64
for rows.Next() {
    var id int64
    rows.Scan(&id)
    ids = append(ids, id)
}

// Step 2: shuffle in the application layer
rand.Shuffle(len(ids), func(i, j int) {
    ids[i], ids[j] = ids[j], ids[i]
})

// Step 3: take the first 10 IDs after shuffling
selectedIDs := ids[:10]

// Step 4: query the full data only for the selected IDs
// IN with 10 IDs uses the PRIMARY index → very fast
query := "SELECT * FROM articles WHERE id IN (?,?,?,?,?,?,?,?,?,?)"
result, err := db.Query(query, selectedIDs...)

When Application Layer Shuffle fits:
  ✓ The total ID count isn't too large (< 100 thousand)
  ✓ Randomness must differ on every request
  ✓ There's caching for the ID list (no need to re-query every request)
  ✓ Full control over the randomization logic in the app

When it doesn't fit:
  ✗ Millions of IDs — transferring all IDs to the app is too heavy
  ✗ Limited application memory
  ✗ No caching for the ID list

An effective combination: cache the ID list in Redis with a TTL of a few minutes. Every request takes the list from cache, shuffles in memory, takes the first 10. The database is only called when the cache expires — not on every request.

Alternative 4: Bucket Sampling with Modulo #

This approach divides the table into “buckets” based on a primary key modulo, then picks a bucket at random. The effect is that the database only needs to read a small fraction of the table.

-- Divide the table into 100 buckets, pick one at random
SET @bucket = FLOOR(RAND() * 100);

SELECT *
FROM articles
WHERE id % 100 = @bucket
  AND status = 'published'
LIMIT 10;

-- Note: if the chosen bucket has fewer than 10 rows,
-- the query returns fewer than LIMIT. Handle this in the app.

For tables with uneven ID distribution, it can be combined with ranges:

-- Pick a random range from the table
SELECT *
FROM articles
WHERE id BETWEEN
    FLOOR(RAND() * (SELECT MAX(id) FROM articles) * 0.9)
    AND
    FLOOR(RAND() * (SELECT MAX(id) FROM articles))
  AND status = 'published'
LIMIT 10;

When Bucket Sampling fits:
  ✓ Very large tables with continuous numeric IDs
  ✓ A little bias in the distribution is acceptable
  ✓ Need a quick solution without schema changes
  ✓ Don't need an exact row count (can be < LIMIT)

When it doesn't fit:
  ✗ Need exactly N rows every time
  ✗ IDs aren't numeric or continuous
  ✗ Distribution must be very even

Performance Comparison of the Four Alternatives #

To help choose the right approach, here’s a direct comparison of the four alternatives based on the most important characteristics:

ORDER BY RAND() Alternatives Comparison:
──────────────────────────────────────────────────────────────────────
  Approach                │ Scalability  │ Randomness │ Complexity
──────────────────────────────────────────────────────────────────────
  ORDER BY RAND()         │ ✗ Very bad   │ ✓ Perfect  │ ✓ Very easy
  ID Sampling (MAX id)    │ ✓ Good       │ ~ Fair     │ ✓ Easy
  Precomputed rand_value  │ ✓ Very good  │ ~ Fair     │ ~ Medium
  App Layer Shuffle       │ ~ Medium     │ ✓ Good     │ ~ Medium
  Bucket Modulo           │ ✓ Good       │ ~ Fair     │ ✓ Easy
──────────────────────────────────────────────────────────────────────

Decision tree:

flowchart TD
    Q1{"Table > 500 thousand rows?"}
    Q2{"Can the schema be changed?"}
    Q3{"Must randomness be perfect on every request?"}

    A1["Precomputed rand_value (best for large scale)"]
    A2["ID Sampling or Bucket Modulo"]
    A3["App Layer Shuffle + Redis ID list cache"]
    A4["ID Sampling is enough"]

    Q1 -->|"Yes"| Q2
    Q1 -->|"No"| Q3

    Q2 -->|"Yes"| A1
    Q2 -->|"No"| A2

    Q3 -->|"Yes"| A3
    Q3 -->|"No"| A4

ORDER BY COUNT: The Same Problem, Different Context #

ORDER BY COUNT is a different pattern but shares a similar root problem: the database is forced to do heavy computation on every query execution, without being able to leverage indexes optimally.

Why ORDER BY COUNT Is Expensive #

-- A query that looks reasonable for "most popular articles"
SELECT a.id, a.title, COUNT(v.id) AS view_count
FROM articles a
LEFT JOIN views v ON a.id = v.article_id
GROUP BY a.id, a.title
ORDER BY view_count DESC
LIMIT 10;

What happens behind the scenes:

The ORDER BY COUNT execution process:
──────────────────────────────────────────────────────────────
  Step 1: JOIN between articles and views
    → Read the entire views table (e.g. 50 million rows)
    → Match each views row to its article

  Step 2: GROUP BY article_id
    → Group 50 million rows by article_id
    → Create a temporary table to store the grouping results
    → Using temporary in EXPLAIN

  Step 3: COUNT(*) per group
    → Count the number of rows in each group
    → An aggregate operation that can't be indexed

  Step 4: ORDER BY view_count
    → Sort the aggregate results
    → Using filesort in EXPLAIN

  Total: this query is re-executed from scratch on every request.
  If this endpoint is called 1000x/minute → the database is exhausted.
──────────────────────────────────────────────────────────────

Solution 1: A Counter Table (Pre-Aggregation) #

This is the best approach for counters queried frequently. Instead of recalculating on every request, store the results in a separate table and update them incrementally.

-- Create a dedicated counter table
CREATE TABLE article_stats (
    article_id  BIGINT UNSIGNED NOT NULL,
    view_count  BIGINT UNSIGNED NOT NULL DEFAULT 0,
    like_count  BIGINT UNSIGNED NOT NULL DEFAULT 0,
    share_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (article_id),
    INDEX idx_article_stats_view_count (view_count DESC)
) ENGINE=InnoDB;

-- Update the counter on every view (in the application layer)
INSERT INTO article_stats (article_id, view_count)
VALUES (?, 1)
ON DUPLICATE KEY UPDATE
    view_count = view_count + 1;

-- The most-popular-articles query: very fast
SELECT a.id, a.title, s.view_count
FROM articles a
JOIN article_stats s ON a.id = s.article_id
ORDER BY s.view_count DESC
LIMIT 10;
-- → type: index on article_stats (uses idx_article_stats_view_count)
-- → No GROUP BY, no aggregates, no filesort

-- ANTI-PATTERN: counting on every request
SELECT a.id, a.title, COUNT(v.id) AS view_count
FROM articles a
LEFT JOIN views v ON a.id = v.article_id
GROUP BY a.id
ORDER BY view_count DESC
LIMIT 10;
-- → Full join, GROUP BY, filesort — re-executed on every request

-- CORRECT: read from the already-aggregated counter table
SELECT a.id, a.title, s.view_count
FROM articles a
JOIN article_stats s ON a.id = s.article_id
ORDER BY s.view_count DESC
LIMIT 10;
-- → Index scan on article_stats, JOIN to articles via PK
-- → No computation — just reading already-calculated data

Solution 2: Batch Aggregation for Eventual Consistency #

For rankings that don’t need to be real-time, run aggregation periodically and store the results:

-- Run via a cron job every 5 minutes or every hour
-- Recalculate stats from the views table
INSERT INTO article_stats (article_id, view_count)
SELECT
    article_id,
    COUNT(*) AS view_count
FROM views
WHERE created_at >= NOW() - INTERVAL 24 HOUR
GROUP BY article_id
ON DUPLICATE KEY UPDATE
    view_count = VALUES(view_count);

-- Clean up old views data if no longer needed
DELETE FROM views WHERE created_at < NOW() - INTERVAL 30 DAY;

This approach fits features like “Trending Today” or “This Week’s Most Popular Articles” — users don’t need to know the data is updated every 5 minutes, not in real time.

Solution 3: Redis for Real-Time Counters #

For counters that must be real-time with high traffic, use Redis as the counter layer and flush to the database periodically:

// In the application layer (Go)

// On every view, increment in Redis (very fast, non-blocking)
func recordView(articleID int64) {
    key := fmt.Sprintf("views:article:%d", articleID)
    rdb.Incr(ctx, key)
    rdb.Expire(ctx, key, 24*time.Hour)
}

// A worker running every 1 minute: flush Redis to the database
func flushViewsToDB() {
    pattern := "views:article:*"
    keys, _ := rdb.Keys(ctx, pattern).Result()

    for _, key := range keys {
        count, _ := rdb.GetDel(ctx, key).Int64()
        articleID := extractIDFromKey(key)

        db.Exec(`
            INSERT INTO article_stats (article_id, view_count)
            VALUES (?, ?)
            ON DUPLICATE KEY UPDATE view_count = view_count + ?
        `, articleID, count, count)
    }
}

// Ranking query: read from the flushed database
// Real-time counters live in Redis, persistence in the DB

ORDER BY COUNT Solutions Comparison:
──────────────────────────────────────────────────────────────────
  Approach           │ Real-time │ DB Load   │ Complexity
──────────────────────────────────────────────────────────────────
  COUNT per query    │ ✓ Yes     │ ✗ Very high│ ✓ Easy
  Counter Table      │ ✓ Yes     │ ✓ Low     │ ~ Medium
  Batch Aggregation  │ ~ 5 min   │ ✓ Low     │ ~ Medium
  Redis + Flush      │ ✓ Yes     │ ✓ Very low│ ✗ High
──────────────────────────────────────────────────────────────────
  Default recommendation: Counter Table
  If traffic is very high: Redis + Flush
  If real-time isn't needed: Batch Aggregation

Anti-Patterns to Avoid #

-- ✗ Anti-pattern 1: ORDER BY RAND() on large tables
SELECT * FROM products WHERE category_id = 5 ORDER BY RAND() LIMIT 6;
-- ✓ Solution: a precomputed rand_value with an index

-- ✗ Anti-pattern 2: ORDER BY RAND() inside a subquery
SELECT * FROM articles
WHERE id IN (
    SELECT id FROM articles ORDER BY RAND() LIMIT 100
);
-- ✓ Solution: get IDs in the app, shuffle, query with IN

-- ✗ Anti-pattern 3: COUNT(*) for rankings in user-facing queries
SELECT category_id, COUNT(*) AS total
FROM products
GROUP BY category_id
ORDER BY total DESC;
-- ✓ Solution: a counter table updated on product insert/delete

-- ✗ Anti-pattern 4: RAND() in WHERE as a sampling filter
SELECT * FROM logs WHERE RAND() < 0.01;  -- take 1% of data at random
-- The database still scans every row, evaluating RAND() per row
-- ✓ Sampling solution: use ID modulo or reservoir sampling in the app

-- ✗ Anti-pattern 5: refreshing rand_value with a single mass UPDATE
UPDATE articles SET rand_value = RAND();
-- Locks the table, can cause downtime
-- ✓ Solution: update in small batches with LIMIT and a loop in the app

Summary #

ORDER BY RAND() is always a full table scan — the database must read, shuffle, and sort the entire table before it can take LIMIT rows. On large tables this is a performance disaster.
RAND() can’t be indexed because its values are non-deterministic — every call produces a different value, so there’s nothing to store in an index.
A precomputed rand_value is the best solution for large tables — add an indexed float column filled on insert, refresh periodically in batches, and query with ORDER BY rand_value.
ID sampling fits quick solutions without schema changes — but the distribution is uneven if many IDs are deleted.
Application-layer shuffling is effective for medium datasets — cache the ID list in Redis, shuffle in memory, query with WHERE id IN (...) leveraging the primary key index.
ORDER BY COUNT has the same problem — aggregate computation repeated on every request isn’t scalable. The solution is separating counter writes from counter reads.
Counter tables are the gold standard for rankings — incremental updates when data changes; queries only read already-aggregated columns without runtime GROUP BY.
Redis + flush fits real-time counters with high traffic — increment in Redis (microseconds), flush to the database periodically, keeping the database light.

← Previous: Type on Join Next: Image on Table →

RAND() #

How ORDER BY RAND() Works Internally #

The Hidden Execution Steps #

Why Indexes Can’t Help #

The Production Impact #

Four Scalable Alternatives #

Alternative 1: ID Sampling with MAX(id) #

Alternative 2: Precomputed Random Value (a rand_value Column) #

Alternative 3: Shuffle in the Application Layer #

Alternative 4: Bucket Sampling with Modulo #

Performance Comparison of the Four Alternatives #

ORDER BY COUNT: The Same Problem, Different Context #

Why ORDER BY COUNT Is Expensive #

Solution 1: A Counter Table (Pre-Aggregation) #

Solution 2: Batch Aggregation for Eventual Consistency #

Solution 3: Redis for Real-Time Counters #

Anti-Patterns to Avoid #

Summary #

Alternative 2: Precomputed Random Value (a `rand_value` Column) #