RE: order by rand() question
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=1; $foo++) {
$s = INSERT INTO mytable SET myfield=. $foo;
mysql_query($s);
}
?
end
produces a table with 1 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
Re: order by rand() question
You asked this earlier this week. The answer has not changed. Matt Heaton wrote: 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 -- Gerald L. Clark [EMAIL PROTECTED] - 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
RE: order by rand() question
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 1 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
Re: order by rand() question
In a slight change of this question (since I have no data to currently test this with, as my ISP is using too old a version of mysql), does anyone know what something like this would do? SELECT * FROM theTable ORDER BY RAND(), date LIMIT 5; I'd like to be able to pull out a certain number of rows, randomly, and then sort them by date (or another field). Or is there a way to sort the rows AFTER they are put through LIMIT? (sort of like how HAVING is to WHERE) Thanks, Chris - 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
RE: order by rand() question
In a slight change of this question (since I have no data to currently test this with, as my ISP is using too old a version of mysql), does anyone know what something like this would do? SELECT * FROM theTable ORDER BY RAND(), date LIMIT 5; Exactly the same as SELECT * FROM theTable ORDER BY RAND() LIMIT 5; would do, since RAND() returns a different value for each row, and the date column would only be sorted in the event of duplicates in the previous order expression. - 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
RE: order by rand() question
Gotcha. So is there any way to return 5 (some number) rows, chosen randomly, and then sort them by date (or name or whatever). So the final result is a list, sorted by date, but of rows chosen randomly from the table. CREATE TEMPORARY TABLE temptable TYPE=HEAP SELECT * FROM theTable ORDER BY RAND() LIMIT 5; SELECT * FROM temptable ORDER BY date; DROP TABLE temptable; - 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
Re: order by rand() question
In a slight change of this question (since I have no data to currently test this with, as my ISP is using too old a version of mysql), does anyone know what something like this would do? SELECT * FROM theTable ORDER BY RAND(), date LIMIT 5; Exactly the same as SELECT * FROM theTable ORDER BY RAND() LIMIT 5; would do, since RAND() returns a different value for each row, and the date column would only be sorted in the event of duplicates in the previous order expression. Gotcha. So is there any way to return 5 (some number) rows, chosen randomly, and then sort them by date (or name or whatever). So the final result is a list, sorted by date, but of rows chosen randomly from the table. I assume this would be possible with some kind of nested SELECT statement, but last I checked, mysql couldn't do this. Thanks, Chris - 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
