Case: Rebuild index in background
⚈ You can afford some background processing
⚈ You don't have to get the most "recent" rows when fetching.
Recreate a random lookup table once an hour:
CREATE TABLE IF NOT EXISTS NewRandom (
rid INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMIARY KEY(rid, other_id)
SELECT id AS other_id FROM tbl ORDER BY RAND(); -- slow, but 'background'
# Note: That table will have 2 columns
DROP TABLE IF EXISTS OldRandom;
RENAME TABLE Random TO OldRandom, NewRandom TO Random; -- atomic and instantaneous
Fetch 10 random rows:
SELECT @max := MAX(rid) FROM Random;
SELECT ... FROM tbl t
JOIN Random r ON r.other_id = t.id
WHERE rid > (@max - 10 + 1) * RAND()
ORDER BY rid
⚈ No 'Bias' flaw.
⚈ May exhibit 'Repitition' flaw until the hour is up.
⚈ May lock table during the hourly rebuild.
⚈ You won't see 'recently' added rows.