I don't think the answer has changed since last week :-)
IMHO it's not "really slow and inefficient", anyway - this script:
---start---
<?php
$db = mysql_connect("localhost", "root", ":-)");
mysql_select_db("Playground", $db);
mysql_query("CREATE TABLE mytable (myfield INTEGER UNSIGNED, PRIMARY KEY
myfield))");
for ($foo=1; $foo<=10000; $foo++) {
$s = "INSERT INTO mytable SET myfield=". $foo;
mysql_query($s);
}
?>
----end----
produces a table with 10000 rows each containing an integer. Then we do:
mysql> SELECT * FROM mytable ORDER BY RAND() LIMIT 5;
+---------+
| myfield |
+---------+
| 9935 |
| 3221 |
| 1530 |
| 3889 |
| 2133 |
+---------+
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.
Cheers
Jon
-----Original Message-----
From: Matt Heaton [mailto:[EMAIL PROTECTED]]
Sent: 15 June 2001 14:23
To: [EMAIL PROTECTED]
Subject: order by rand() question
Hi all, trying to do something and have it be as efficient as possilble. My
question is if I have a table with say 10,000 rows in it, and I issue
a command like this
select * from table where number=1 order by rand() limit 1;
If 1000 rows would match this command does mysql first find all 1000 rows
and THEN randomly select 1 of those to return, OR does it just randomly
start somewhere and then stop when it finds one? The first method would
be really slow and inefficient, but I am afraid that is how it works? Is
there anyone out there that knows FOR SURE how the order by rand() operation
works? I can't find a specification out there for it.
Thanks,
Matt Heaton
---------------------------------------------------------------------
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
**********************************************************************
'The information included in this Email is of a confidential nature and is
intended only for the addressee. If you are not the intended addressee,
any disclosure, copying or distribution by you is prohibited and may be
unlawful. Disclosure to any party other than the addressee, whether
inadvertent or otherwise is not intended to waive privilege or
confidentiality'
**********************************************************************
---------------------------------------------------------------------
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
