Summary
Add a rule to detect ORDER BY RAND() (or RANDOM(), NEWID()) which is one of the worst-performing SQL patterns.
Why This Matters
ORDER BY RAND() is algorithmically terrible:
- O(n log n) every time — generates random value for each row, then sorts
- No caching possible — result changes each execution
- Full table scan — must process every row regardless of LIMIT
- Memory pressure — large temp tables for sorting
Performance Disaster
-- This innocent-looking query
SELECT * FROM products ORDER BY RAND() LIMIT 5;
-- Actually does this:
-- 1. Scan ALL rows in products (even if millions)
-- 2. Generate random number for each row
-- 3. Sort entire result set
-- 4. Return first 5 rows
-- Time: O(n log n) where n = total rows
Benchmark Example
| Table Size |
ORDER BY RAND() |
Optimized Random |
| 10K rows |
50ms |
2ms |
| 100K rows |
800ms |
2ms |
| 1M rows |
12 seconds |
3ms |
| 10M rows |
3 minutes |
5ms |
Rule Specification
| Property |
Value |
| Rule ID |
PERF013 |
| Name |
ORDER BY RAND() detected |
| Category |
Performance |
| Severity |
Warning |
| Default |
Enabled |
Detection Patterns
-- Should trigger
SELECT * FROM users ORDER BY RAND(); -- MySQL
SELECT * FROM users ORDER BY RANDOM(); -- PostgreSQL
SELECT * FROM users ORDER BY NEWID(); -- SQL Server
SELECT * FROM users ORDER BY DBMS_RANDOM.VALUE; -- Oracle
-- With LIMIT (still bad, just less obviously)
SELECT * FROM products ORDER BY RAND() LIMIT 10;
Suggested Message
"ORDER BY RAND() requires full table scan and sort. For random selection, consider: (1) WHERE id >= FLOOR(RAND() * max_id) for single random row, (2) pre-generated random column with index, or (3) application-side randomization with OFFSET."
Better Alternatives
-- Alternative 1: Random offset (if you know approximate count)
SELECT * FROM products LIMIT 1 OFFSET FLOOR(RAND() * (SELECT COUNT(*) FROM products));
-- Alternative 2: Random ID range (if IDs are sequential-ish)
SELECT * FROM products WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM products)) LIMIT 5;
-- Alternative 3: Pre-computed random column (best for frequent random access)
-- Add column: random_sort FLOAT DEFAULT RAND(), indexed
SELECT * FROM products ORDER BY random_sort LIMIT 5;
References
Implementation Notes
- Detect:
ORDER BY RAND(), ORDER BY RANDOM(), ORDER BY NEWID()
- Detect Oracle:
DBMS_RANDOM.VALUE, DBMS_RANDOM.RANDOM
- Severity: Warning (there are legitimate small-table uses)
- Include specific alternatives in suggestion based on LIMIT presence
Summary
Add a rule to detect
ORDER BY RAND()(orRANDOM(),NEWID()) which is one of the worst-performing SQL patterns.Why This Matters
ORDER BY RAND() is algorithmically terrible:
Performance Disaster
Benchmark Example
Rule Specification
PERF013Detection Patterns
Suggested Message
Better Alternatives
References
Implementation Notes
ORDER BY RAND(),ORDER BY RANDOM(),ORDER BY NEWID()DBMS_RANDOM.VALUE,DBMS_RANDOM.RANDOM