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:
FROM ptg_users

MAX( ) – MIN( ) ) * RAND( )
) + MIN( ) AS rid
FROM ptg_users
) AS x ON >= x.rid

Replace ptg_users with the name of your table.

Leave a Reply

Your email address will not be published.