At 11:32 AM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 18:02, schrieb mos:
> At 10:34 AM 10/24/2011, you wrote:
>> select id from table order by rand() limit 1;
>> is doing as example a dumb temporary table with the full size
>
> Because it has to sort the entire table, then it returns the one
row. This of course is extremely inefficient. :)
> You need to choose a random row by using an auto-inc field. Something like:
>
> select id from table where id>=myrandomnum limit 1
but this is TOTALLY braindead if "id" is a primary-KEY with auto-increment
It all depends on how many holes you have in the sequence and how
random you want the selections to be. If there are no holes then it
will work. You need of course to get the first and last id and
generate "myrandomnum" within that range. If there are a lot of holes
in the sequence then build another table with the columns bin and an
autoinc column and pick one of those rows randomly. Regenerate the
table once an hour or once a day.
Either way it is going to be a LOT FASTER than sorting the entire table.
Miike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
