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
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
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
]]
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
"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
