> > > I need query to be ORDER BY id DESC. I've dropped this ORDER BY to simplify > > my case. But in real-life app I need it. > > > > So, index on (kind, computer) has these index records: > > > > [ KIND ] [ COMPUTER ] [ ID ] > > I don't know that SQLite does an inherent addition of the 'id' column to all > INDEXes. Some index implementations do this because their algorithm requires > that all positions in an index are unique, but I don't think SQLite works > like that. I think that if you want 'id' to be part of an index you have to > say that in the definition.
SQLITE always stores rowid in every index record. This rowid let SQLITE find approriate database record. You can read more here: http://www.sqlite.org/queryplanner.html > > Nevertheless this still doesn't explain why your first run of a query is so > much slower than subsequent runs. SQLite's cache size is set here: > > http://www.sqlite.org/pragma.html#pragma_cache_size > > If the cache size described there doesn't explain the behaviour you're > seeing, the problem isn't with SQLite, it's in your application or the OS or > your hardware. The problem is filling the cache. SQLITE wants too much data to execute simple query. Cache size of SQLITE doesn't matter because OS does it's own caching. After query has been run for a first time OS caches disk pages. And subsequent queries immediately gets data from RAM not from disk. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
