> 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
