On 14 Oct 2011, at 2:59pm, Dan Kennedy wrote: > On 10/14/2011 07:40 PM, Simon Slavin wrote: >> >> I'm sorry, I completely missed the 'data = 10' earlier. If you have an >> index on the data column then that's the index SQLite would used for that >> query. Once it has picked that index it no longer has access to the rowid >> index. >> >> If you want an index that would be ideal for >> >> SELECT * FROM table WHERE data = 10 ORDER BY rowid LIMIT 250 OFFSET 500000 >> >> it would be one index on the two columns together: >> >> CREATE INDEX tdr ON table (data, rowid) > > This is a logical conclusion to draw, but it is not actually > necessary. All indexes created by SQLite implicitly have the > rowid as the final column. So all you really need for the > query above is: > > CREATE INDEX tdr ON table1(data);
I understand your reasoning, but what the OP reports suggests that SQLite is not working this way. > Note that this: > > CREATE INDEX tdr ON table1(data, someothercolumn); > > would not work quite as well. Since the index would not help > with the ORDER BY. The precise index I recommended was specially picked for the precise SELECT I mentioned. The 'othercolumn' is, in fact, the column that the OP wanted in his 'ORDER BY'. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
