"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's a rather nasty
> sit-'n-spin loop. Instead of being linear with the size of the random
> number, it's linear with the size of the table, but the constant multiplier
> is much smaller. For all but the largest and/or simplest tables, that
> should be a win.
This is broken for me - but, I've learned you can actually set variables
in SQL which just opened a HUGE door for me :) I'm going to study more
about this ASAP. ***Thanks for the suggestion.*** I get the following:
mysql> select * from table where (@rand:=@rand-1)+id=id;
ERROR 1064: You have an error in your SQL syntax near 'table where
(@rand:=@rand-1)+id=id' at line 1
> By my measurements, this is a good 3x faster than the LIMIT $rand, 1
> approach on my test table. But if "id" is indexed, Carsten's no-calc
> approach still blows it away.
Carsten's approach is one of those "duh" things I don't understand why I
hadn't thought of it. It's fast, and very closely approximates what I
want to do.
> Is it possible to do a "fair" match without incurring at least one full
> pass through the table?
This is "the question" isn't it. At a minimum, you must know the # of
rows you have to choose from; depending on your indexes this may not
require a full pass through. But then you must (ideally) randomly
access any single row in that set.
So maybe it cannot be done under MySQL.. What about Oracle, MS SQL, and
Postgres?
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
