--- Dennis Cote <[EMAIL PROTECTED]> wrote: > >>> select * from table1 order by random(id) limit 1 ... > This does work but it requires duplicating the entire table into a > temporary table which also has the random number assigned to each row, > and then sorting it. This is very expensive for a large table. > > duplicate O(N) + sort O(N log N) + select O(1)
It used to be that way before 3.3.0 or so, but now the entire table is not duplicated with an ORDER BY ... LIMIT 1. No more than (OFFSET + LIMIT) records are retained in the working set during a single pass over the table, or in this case just a single record. You can verify this by setting PRAGMA temp_store = MEMORY; and observing the memory use during the query. See comments in select.c for more details. ____________________________________________________________________________________ No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
