Optimize MySQL order by RAND()

It is not recommended to use ORDER BY RAND() only, it will slow for query large database like over 50,000 rows. The easiest way to optimize existing code, just add
RAND()< (SELECT ((1/COUNT(*))*10) FROM Table_name)
after the WHERE before the ORDER, this sub query will speed up the select random from the table.

Using this way, you no need to worry the database that without ID and Auto INC too.

SELECT * FROM `Table_name` WHERE
RAND()<(SELECT ((1/COUNT(*))*10) FROM Table_name WHERE `field` = 'value' )
ORDER BY RAND() LIMIT 1;

Tags:

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.