Part 29: Selecting Random Records & Performance Traps

Need to show a "Random Featured Product" on your homepage? Beware the classic ORDER BY RAND() trap.

1. The Anti-Pattern: ORDER BY RAND()

-- ❌ DISASTROUS on tables with > 10,000 rows!
SELECT * FROM products ORDER BY RANDOM() LIMIT 1;

Why? The database assigns a random number to every single row in the table, sorts the entire table, and then takes one row. This triggers a full table scan and disk sort.

2. High-Performance Alternative

Generate a random ID in application code or calculate via offsets:

SELECT * FROM products 
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM products))) 
ORDER BY id LIMIT 1;

Key Takeaways

• Never use ORDER BY RAND() or ORDER BY RANDOM() on large production tables.

In Part 30, we conclude Module 3 with a Hands-On Product Catalog Query Project!