ORDER BY RAND（）的性能命中率？ [重复]

Question

Possible Duplicate:
MySQL: Alternatives to ORDER BY RAND()

I'm pulling about 20 random rows, most or all with WHERE clauses attached, in a table of 1000~2000 records. This won't grow by much, maybe 150 every year.

I'm also looking at a few thousand users at most, but with this query being part of a popular area of the site.

Do I really need to be concerned about performance hits where ORDER BY RAND() is concerned? I've heard all the horror stories about performance issues, but I haven't found much when such a small table is being hit constantly.

I can't do a cache for this.

Answer 1

The ORDER BY RAND() is easily used and forgotten, but you have to realize that MySQL needs to inspect every row because RAND() is not constant.

Inspecting 1000 rows shouldn't be a problem, but keep an eye on the query performance over time. If you just needed one record, you could have found an answer here: ORDER BY RAND() alternative

Edit

You said you can't do caching, but even a small generated .php file is a cache :)

For instance, you can store a list of ID's in a cache; you update it every time you insert or remove a database record. Then, you pull 20 random ID's using PHP and use those to query the database (using e.g. IN (4, 7, 10, 45, etc.)

Update

As pointed out by OP, this is not possible if the query also has conditions (i.e. not every item in the table is eligable to be picked).