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 _______________ SELECT pri_key FROM table ORDER BY RAND() LIMIT 1; let's say 'pri_key' as a autoincrement integer let's say the table has some blob-fields and is 4 GB large fetch ONE random id via mysql will explode your webserver because 4 GB data is copied - there is no valid reason for such crappy implementation, really no single reason
signature.asc
Description: OpenPGP digital signature
