minte9
SoftwareDevelopment / MYSQL





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;

Daily Rand

Get random item by seed (day). 
 
SELECT * FROM article ORDER BY rand("20190131") # date(Ymd)

Order by array

Get items from array, then order by field. 
 
SELECT * FROM documents 

    WHERE id IN (3, 7, 5) ORDER BY FIELD (id, 3, 7, 5)






Questions and answers:
Clink on Option to Answer




1. Is it best practice to order by RAND()?

  • a) YES, it's quick
  • b) NO, it's very slow

2. How do you order by rand daily?

  • a) ORDER BY rand("daily")
  • b) ORDER BY rand("20190131")

3. You can optimize rand with

  • a) INSERT ... RAND()
  • b) SELECT ... ORDER BY RAND()

4. How do you order by array?

  • a) ORDER BY id IN (3,7,5)
  • b) ORDER BY FIELD(id, 3,7,5)


References: