?php
$query = "SELECT col1, col2 FROM the_table ORDER BY RAND() LIMIT 1";
$result = mysql_query($query) or die("could not query");
$row = mysql_fetch_array($result);
print $row[col1];
print "P";
print $row[col2];
?
Robert B. Barrington
GetMart Commercial Ecom: Web Administrator
http://weddinginlasvegas.com/
http://getmart.com/
[EMAIL PROTECTED]
Vegas Vista Productions
3172 North Rainbow Boulevard
Suite 326
Las Vegas, Nevada 89108-4534
Telephone: (702)656-1027
Facsimile: (702)656-1608
-Original Message-
From: Stephen Waits [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 10, 2001 12:13 PM
To: [EMAIL PROTECTED]
Subject: ORDER BY RAND() Too Slow! Alternatives?
Hi there,
In the quest to get a random row from a table, "order by rand()" has
proven too inefficient and slow. It's slow because MySQL apparently
selects ALL rows into memory, then randomly shuffles ALL of them, then
gives you the first one - very inefficient. There are a few other ways
I've thrown around but none are "elegant".
One is, if a table has an id # column, like "id int unsigned not null
auto_increment", I could do this:
select max(id) from table;
$random_number = ...
select * from table where id=$random_number;
This is very fast (assuming the id field is a unique index). But it has
the problem that if records have been deleted I might get a 0-row
response. It also does not work if I want to limit to a particular
category, for instance "where category='women'" or something.
I could do this too:
select count(*) from table;
$random_number = ...
select * from table limit $random_number,1;
This has the benefit of always working but the speed, though faster than
the "order by rand()" method, remains unacceptable. The speed seems
linear with regard to the size of $random_number; which is probably
obvious to you.
So I've experimented with several other things:
select * from table where limit rand(),1;
select * from table where id=(mod(floor(rand()*4294967296),count(*))+1);
.. and it only gets uglier from -- these are all not accepted by MySQL.
MySQL does not allow for subqueries which is another way it could possibly
be accomplished. In the end, I'll just use what works, no matter the
speed.
BUT, I'd love to hear what other people have done to solve this problem!
Thanks,
Steve
-
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
-
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
