Faster Way to Get a Random Row MySQL
So doing order by RAND() limit 0,1 is pretty slow for big tables. So I came up with the following. Basically it uses the ID column to find a random id number between the lowest id in the table (usually it will be 1 since that’s where auto increment start; however, I lots of times delete these rows during testing and don’t reset my auto incrementer). The only stipulation is that your ID doesn’t have any “holes” – basically if you’ve deleted rows in the middle of your table so that the ID column has a set such as n,n+1, n+y,n+y+1, n+y+2… where y > 1. Example would be 1,2,3,5,6,7. Unfortunately, if you are actively deleting rows in the middle of your database you will have to resync a counter column (either the id or some or column) to fill up the holes… Unfortunately, that’s pretty expensive (sort of like reindexing an array to take off holes… same idea). But maybe you can find a balanced solution. Who knows.
Here’s the code:
SELECT *
FROM ptg_users
JOIN (
SELECT ROUND( (
MAX( ptg_users.id ) – MIN( ptg_users.id ) ) * RAND( )
) + MIN( ptg_users.id ) AS rid
FROM ptg_users
) AS x ON ptg_users.id >= x.rid
LIMIT 1
Replace ptg_users with the name of your table.
2 comments to “Faster Way to Get a Random Row MySQL”
March 5th, 2010 at 10:39 am
Nice workaround, thanks for sharing.
Other solution will be(with php/mysql):
$total_rows = your_query_invoker(“SELECT Count(*) FROM your_table”);
$pos = rand(0,$total_rows);
$sql = “SELECT * FROM your_table WHERE something LIMIT $pos,1″
The most important thing is that the rand is in php, and consume few CPU. I know that need two calls to MySQL, but if not close the socket connection it is not a big problem.
March 10th, 2010 at 7:51 am
Another solution is on my blog at http://rhcms.de/blog.php
Nice workaround anyway.