At 11:39 PM 2/10/2001 -0800, Stephen Waits wrote:
>Never mind on the "it doesn't work on my system" more like it didn't
>work on my brain :) Works fine.
Oh, phew.
>Theoretically it could be as fast as Carsten's method couldn't it? If
>it hit a record on the first shot? Otherwise it's pounding
"Jeffrey D. Wheelhouse" wrote:
>
> SELECT @lines:=COUNT(id) FROM table;
> SET @rand=CEILING(RAND()*@lines);
> SELECT * FROM table WHERE (@rand:=@rand-1)+id=id;
Never mind on the "it doesn't work on my system" more like it didn't
work on my brain :) Works fine. And now that I ponder it a bit
"Jeffrey D. Wheelhouse" wrote:
>
> Here's another approach. I'm curious about the performance implications:
>
> SELECT @lines:=COUNT(id) FROM table;
> SET @rand=CEILING(RAND()*@lines);
> SELECT * FROM table WHERE (@rand:=@rand-1)+id=id;
>
> This *should* give each row an equal chance, but it'
Hmm, no reading comprehension points for me. I managed to read Steve's
message the first time without realizing that he only wanted one row.
Here's another approach. I'm curious about the performance implications:
SELECT @lines:=COUNT(id) FROM table;
SET @rand=CEILING(RAND()*@lines);
SELECT
";
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-
> 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
Could you do something like:
CREATE TEMPORARY TABLE temptable (
pk INTEGER,
rand INTEGER
);
INSERT INTO temptable SELECT yourpk,Rand() FROM yourtable;
SELECT yourtable.* FROM yourtable,temptable WHERE pk=yourpk ORDER BY rand;
DROP TABLE temptable;
That might be quicker than your current a
