At 03:32 AM 10/25/2011, you wrote:
>Am 25.10.2011 05:45, schrieb mos:
> > At 05:31 PM 10/24/2011, Reindl Harald wrote:
> >
> >
> >> Am 24.10.2011 23:31, schrieb mos:
> >> > 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
> >>
> >> and why in the world is with the query above the WHOLE table
> >> copied in a temp-table while fecth the whole id-list in a
> >> php-array and take a random one is more than 1000 times faster?
> >>
> >> the implementation if "order by rand()" is totally braindead
> >
> > It is not "braindead". You told MySQL to sort by rand() which is
> a non-indexed column.
> > It needs to assign a value to each row of the result set (all ids
> of the table) and sort
> > it to get the lowest random number. This is very inefficient for
> large tables.
>
>but there is mo need to do this with the whole table
>if the only requested field is the primary key
Sure but if the table has 100 million rows and you want 1 random id,
that means sorting 100 million id's from the index to disk. This is
still grossly inefficient. It may work fine on tables with a couple
thousand rows, but not for million row tables. That's why the two
methods I suggested don't use sorting.
Mike
