On Tue, 2005-09-06 at 13:07 -0700, R S wrote: > Hi, > I have a million records in my DB. I tried using .explain on and ran the > query below which took a long time although I just want the last 100,000 > records...(It ran much faster when my table had a 100,000 records) so I > assume it is related to how I constructed the statement. > > select columns from myTable WHERE (rowid > (select max(rowid) from myTable) > - 100000) and many more conditions group by myTable.column1, myTable.column2 > > explain just returned me the columns headers? >
The ".explain on" command is a special command to the "sqlite" shell that just sets up output formatting in a way that makes the output of the EXPLAIN command look pretty. You still have to run the EXPLAIN command separately: EXPLAIN select columns from .... Your query above assumes that ROWIDs are consecutive, which may or may not be the case. (Probably it is not the case.) To get the last 100000 rows, I would suggest this: SELECT * FROM mytable ORDER BY rowid DESC limit 100000; That will work as long as the optimizer doesn't try to use an index to satisfy terms of your WHERE clause. -- D. Richard Hipp <[EMAIL PROTECTED]>
