Random() In MySql? [closed]

Closed. This question needs details or clarity. It is not currently accepting answers. 开发者_开发百科

---

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 6 years ago.

Improve this question

Is it possible to retrieve random rows from table X where flags==0? Using MySql and C#

---

SELECT *
FROM X
WHERE flags = 0
ORDER BY rand()
LIMIT 1

This retrieves 1 random row. Replace 1 by N to get N random rows.

Caveat: As others pointed out this can be slow as it needs a full table scan. I used to do this with DB2, where this worked perfectly for tables with hundreds of thousand of rows, but according to the link in tereško's answer, MySQL seems to degrade much quicker.

---

You should never, ever use ORDER BY RAND(). It gets really really slow as the size of table grows. Instead you should read this article.

In case you have irrational fear of learning, here is there solution which would do what you ask for:

SELECT 
    X.x_id,
    X.foobar
FROM X
  JOIN ( 
    SELECT CEIL(RAND()*(SELECT MAX(x_id) FROM X)) AS x_id
  ) AS Choices
  USING ( x_id )

WHERE X.x_id >= Choices.x_id
  AND X.flags = 0

ORDER BY X.x_id LIMIT 1;