0

I'm looking to get a random row from MySQL without using too much time or resources on the system. I don't care about weather the code given is PHP or MySQL based, however please note there are 'gaps' in my table.

My table columns are 'id' (Primary key, auto increment), varchar, int, int

I'd like it to be as random as possible

smdvlpr
  • 1,088
  • 9
  • 22

3 Answers3

2

The good solution was in What is the best way to pick a random row from a table in MySQL? question.

Community
  • 1
  • 1
Ivan Nevostruev
  • 28,143
  • 8
  • 66
  • 82
1

I would do it in two queries, and three steps overall.

  1. Find the number of rows in the table using SELECT COUNT(*) FROM your_table
  2. Use PHP's math functions to find a random number between 0 and the number of rows
  3. Get the actual data you want using SELECT * FROM your_table LIMIT 200, 1, where 200 is the random number you calculated in step 2.
mwalling
  • 1,745
  • 1
  • 19
  • 26
  • 1
    You could do all that within a stored procedure, saving a trip to get the count & back. If the database is on a different host, you additionally alleviate the webserver load so it's available for what it is meant for... – OMG Ponies Nov 10 '09 at 20:18
  • True. I don't use stored procedures on a regular basis, so I don't think about using them where they would be useful :) – mwalling Nov 10 '09 at 20:33
0

You can use:

SELECT id FROM table ORDER BY RAND() limit 1
Andrei Serdeliuc ॐ
  • 5,828
  • 5
  • 39
  • 66
  • 2
    No thanks, this table has much too many rows for that to be effective – smdvlpr Nov 10 '09 at 19:59
  • How can it not be greedy? If we're trying to get a single row from a table with gaps, the only way to A) get a random record and B) ensure that we do return a valid record is to be exhaustive - i.e. consider the set of all primary keys in the table, and pick one at random. Using probabilistic methods, you're not guaranteed to return a valid record. If, however, that's not a concern (if you don't require that you always get a valid record back), then mwalling has the solution below. – Chris Tonkinson Nov 10 '09 at 20:10
  • 1
    Except for the case where your "random" number is very close to the size of the table and rows have been deleted in between the two queries causing the offset to exceed the table size, how are you not going to get a valid record? – mwalling Nov 10 '09 at 20:32