Thursday, 5 January 2012

Rails/Mysql - Selecting Random records

The straight forward and the most reliable solution to select random record from a table would be to use rand() in Mysql or random() in Postgres.

However the glitch is with the performance of rand() when using against large tables (even as 1000 rows). With my test results of 750 rows.

Photo.order('rand()').limit(30)  - took 9ms

Photo.where(:id => Photo.order('rand()').limit(30).select(:id).collect(&:id))  - took 2.5ms

So using rand() with selecting just the id, is atleast 3 times faster.

No comments:

Post a Comment