Best IT Recipes

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

Possible duplicate:
quick random row selection from large table in mysql

I have seen random rows being pulled using such queries that are pretty inefficient for large datasets.

SELECT id FROM table ORDER BY RANDOM() LIMIT 1

I also saw various other RDBMS-specific solutions that do not work with MySQL.

The best I can think of doing this is to use two queries and do something like this.

  • Get the number of rows in the table. MyISAM tables store row counts, so it is very fast.
  • Compute a random number between 0 and rowcount - 1.
  • Select the row ordered by the primary key using LIMIT randnum, 1

Here's the SQL:

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

Does anyone have a better idea?

+9
mysql random
Sep 26 '08 at 21:58
source share

No one has answered this question yet.

See similar questions:

44
MySQL: alternatives to ORDER BY RAND ()
42
quick random row selection from large table in mysql
8
Quickly select random id from mysql table with millions of inconsistent records
3
Return random rows from mysql database without using rand ()
2
The fastest way to get a random string from mySQL
one
ORDER BY RAND causing a very high load
one
MySQL Random lines. Convert strings to PHP arrays.
one
Random line, but with next and previous button
0
Random MySQL string with PHP low system resources
0
Is there a way to get selected random records using a query from a table in mySql

or similar:

1069
Is it possible to combine multiple MySQL rows into one field?
704
What is the best sorting for MySQL with PHP?
425
MySQL selects 10 random rows from 600K rows quickly
379
How to add indexes to MySQL tables?
159
MySQL - Get row number on selected
104
Select the last N rows from MySQL
42
quick random row selection from large table in mysql
17
Choosing random rows from mysql table
0
Select 10 random rows in mysql in the most efficient way in a large table
0
Select random entry from mysql database, best method?


More articles:


All Articles