Order by rand
Order by rand is very slow, use Order by index (random).
# SELECT * FROM article ORDER BY RAND() LIMIT 1; # very slow
ALTER TABLE article ADD random FLOAT NOT NULL;
ALTER TABLE ADD INDEX random (random);
UPDATE article SET random = RAND();
INSERT INTO article (..., random) VALUES (..., RAND());
SELECT * FROM article WHERE random >
(SELECT RAND() as rand) ORDER BY random LIMIT 1;
Questions and answers
Is it best practice to order by RAND()?
- a) YES, it's quick
- b) NO, it's very slow
How do you order by rand daily?
- a) ORDER BY rand("daily")
- b) ORDER BY rand("20190131")
You can optimize rand with
- a) INSERT ... RAND()
- b) SELECT ... ORDER BY RAND()
How do you order by array?
- a) ORDER BY id IN (3,7,5)
- b) ORDER BY FIELD(id, 3,7,5)