MySql query for random id [duplicate]

This question already has answers here: Closed 11 years ago.

Possible 开发者_开发百科Duplicate:

MySQL select 10 random rows from 600K rows fast

I need to pull from a table a random id. At this moment only a few records exists but with time they will grow. What methods of getting this id are in Php or MySql, and what are the trade offs, consequences between them. One last thing i need speed and performance.

---

select * from YOUR_TABLE order by rand() limit 1

---

You can achieve this direct in your SQL:

SELECT `idfield` FROM `table` ORDER BY RAND() LIMIT 0,1;

Also see here for some alternatives.

This will be a simple means of execution than building the randomisation in your PHP and then passing to mySQL, though the link above details the merits of the various approaches.

---

SELECT id FROM table ORDER BY RAND() LIMIT 1

What this does is basically: take the table, order the records by a random number, and get the first record's ID.

---

There is already a lot of questions and answer about that:

Random Records Mysql PHP

mysql query with random and desc

If you have less that 500,000 records, the RAND() is perfectly fine.

SELECT * FROM tbl_name ORDER BY RAND() LIMIT 1 

---

Well you can obtain a random number with RAND(), but you would still have to call that method/function until you actually get something (you can have 1,2,5,6,100 as id`s due to deletion). there is a post on this http://jan.kneschke.de/projects/mysql/order-by-rand/ Procedures can be usefull (but said to be slow...): 1 var to get the maximum value then produce a random within 1 and that interval (or get the minimum) then query