> I don't think the answer has changed since last week :-)
>
> IMHO it's not "really slow and inefficient", anyway - this script:
(clipped)
> produces a table with 10000 rows each containing an integer. Then we do:
>
> mysql> SELECT * FROM mytable ORDER BY RAND() LIMIT 5;
(clipped)
> 5 rows in set (0.08 sec)
>
> Right, that's *less than a tenth of a second*, and that's on a crufy old
> P200 that also happens to be serving our company Intranet at the same time
> as doing this little experiment.
>
> So what's so inefficient? I can't blink in 0.08 seconds, let alone think a
> query is running too slowly.
Watch out though. Sure, it may be that fast when you're selecting one row
out of 10,000 ints on a lightly loaded server. However MySQL works this way:
- SELECT * FROM mytable;
- Order all found rows by rand()
- Send back the first 5
It's not that inefficient when you just have one column, since that's all
you'll be getting anyways, however I have a table with 38 columns, 141000
rows and lots of data you are making MySQL get a lot of data it doesn't end
up using. This is how fast your fast query runs on it (granted this is on a
server with medium to heavy load and ~50 queries per second):
mysql> select * from users order by rand() limit 1;
...
1 row in set (1 min 29.48 sec)
You can make this a little faster:
mysql> select id from users order by rand() limit 1;
...
1 row in set (10.10 sec)
mysql> select * from users where id = 123456;
...
1 row in set (0.12 sec)
But it's still quite inefficient. The best way would be to generate the
random numbers from your application. If you wanted five rows, generate 10
numbers (just to be really safe in case a few reference deleted rows)
between 1 and max(id) (assuming you have an auto_increment column named id)
and do:
mysql> select max(id) from users;
...
1 row in set (0.12 sec)
mysql> select * from users where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) limit
5;
...
5 rows in set (0.33 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
