> > > The sqlite3_stmt_status() interface was designed for this purpose. > http://www.sqlite.org/c3ref/stmt_status.html >
Thanks for the info, I hope it will be extended also with other counters I did some test with this Rowid/Id trick. A larger base, 22mb, 100,000 records (220 bytes per record average) and the difference for "SELECT rowid .." query was more noticable (20 mb read without Id index, 1mb with it). But I also noticed that sometimes sqlite doesn't allow to use explicit index with ORDER BY clause considering them (primary and explicitely created) equivalent. I think it's not a bug, but in terms of performance maybe it would be more appropriate to obey ORDER BY clause in this case. The table is CREATE TABLE [MainData] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT,... The index is CREATE INDEX [idx_ID] ON [MainData] ([ID] ) Different examples (the second line is EXPLAIN QUERY PLAN result): SELECT rowid FROM MainData ==== TABLE MainData 22 Mb read (expected, Primary index used) SELECT rowid FROM MainData ORDER BY Id ==== TABLE MainData WITH INDEX idx_ID ORDER BY 1,2 Mb read (expected, idx_ID used) SELECT rowid FROM MainData Where Id > 1 AND Id < 300000 ==== TABLE MainData USING PRIMARY KEY 22 Mb read (expected, Primary index used) SELECT rowid FROM MainData Where Id > 1 AND Id < 300000 ORDER BY Id ==== TABLE MainData USING PRIMARY KEY ORDER BY 22 Mb read (not fully expected, ORDER BY points to Id field, but its actually an alias) SELECT rowid FROM MainData INDEXED BY idx_ID Where Id > 1 AND Id < 300000 ORDER BY Id ==== TABLE MainData WITH INDEX idx_ID ORDER BY 1,2 Mb read (expected, but INDEXED BY is not recommended for query tuning) So when the query contains WHERE clause only when INDEXED BY was added this explicit index was used. As for use usefullness of this trick, I think it really can be useful when the application wants to load one "virtual" long list of data actually loading only rowids and optionally query full data record for visible rows. I think in this case 22 MB disk access vs 1 MB disk access makes difference Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
