What is the best way to pick a random row from a table in MySQL?

Mar 12, 2012   //   by phpfreelancer.biz   //   Blog, MySQL Tutorial  //  No Comments

I have seen random rows pulled using queries like this, which are quite inefficient for large data sets.

SELECT id FROM table ORDER BY RANDOM() LIMIT 1

Get the number of rows in the table.
Calculate a random number between 0 and rowcount – 1.
Select a row ordered by primary key, with a LIMIT randnum, 1

Here’s the SQL:

SELECT COUNT(*) FROM table;
SELECT id FROM table LIMIT randnum, 1;

Leave a comment

Share This Post

RSS Wordpress News