minte9
LearnRemember   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)




References: