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 * 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.
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.
Is it possible to do a "fair" match without incurring at least one full
pass through the table?
Jeff
At 10:11 PM 2/10/2001 +0100, Carsten H. Pedersen wrote:
> > 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;
>
>How about
> select * from table
> where id>$random_number
> order by id
> limit 1;
>
>(note that I'm using '>' rather than '='). This should always work,
>and be pretty fast. There is a caveat, tho': this won't work if
>you need "exact randomness", i.e. certain records will have a
>better chance of being selected than others. This gets worse,
>the larger "holes" are in sets of deleted id's.
>
>/ Carsten
>--
>Carsten H. Pedersen
>keeper and maintainer of the bitbybit.dk MySQL FAQ
>http://www.bitbybit.dk/mysqlfaq
>
>
> >
> > 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
---------------------------------------------------------------------
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
