Mysql 1 Random Row

Question

Possible Duplicate:
How to request a random row in SQL?

Is this the correct way to do this?

$query = 'SELECT * FROM gameids ORDER BY timestamp RAND LIMIT 1';

Answer 1

Incorrect. You cant order by a column (afaik) if you want it to randomize.

$query = 'SELECT * FROM gameids ORDER BY RAND() LIMIT 1';

Answer 2

You don't need to tell it which column to randomise, but you do need () after RAND because it is a function.

SELECT
  * 
FROM
  gameids 
ORDER BY 
  RAND()
LIMIT 1

Answer 3

RAND is a function and its not effective in big tables, because it does not use indexes.

$query = 'SELECT * FROM gameids ORDER BY RAND() LIMIT 1';

One possible solution is to add column called random and on every record generate random number for it, then when you are querying the database, order by this column and you'll get pseudo-random but this time using the indexes.

$query = 'SELECT * FROM gameids ORDER BY timestamp, random LIMIT 1';

Edit: You can also make RAND() more "flexible" by applying some expression like this RAND() * MAX(numeric_column_name)

If you are interested in optimizations, take a look at this blog post: http://jan.kneschke.de/projects/mysql/order-by-rand/

@Marwelln is correct.