Hi guys, I tried the proposed thing to change the index to this:
CREATE INDEX IDX_event_1 ON event (deleted, major); Now EXPLAIN QUERY PLAN returns: explain query plan select e.type, e.id, e.rsub_id, e.person_id, e.timestamp, e.file_id, e.previous_name, e.previous_folder_id, e.transparent, e.folder_id from event e where e.deleted = 0 and major = 0 and e.id > 330557 order by e.id asc limit 1 selectid order from detail 0 0 0 SEARCH TABLE event AS e USING INDEX IDX_event_1 (deleted=? AND major=? AND rowid>?) But the time from my test scenario is in the same range as before: 2014-Mar-26 11:11:44.456076:NORM: AGENT REV: 330557:330556. TIME FOR 1000 ITERATIONS: 39239 milliseconds ================================================================================ Several additional things that could be helpfull: I ran ANALYZE on the original DB and the EXPLAIN QUERY PLAN returned that a different index will be used: selectid order from detail 0 0 0 SEARCH TABLE event AS e USING INTEGER PRIMARY KEY (rowid>?) I know that I mentioned the above information in my previous email, but I forgot to mention the timing results with this db (nothing else is changed, just the db is analyzed) 2014-Mar-26 11:24:15.954625:NORM: AGENT REV: 330557:330556. TIME FOR 1000 ITERATIONS: 234 milliseconds If I run ANALYZE on the db with the new index INDEX IDX_event_1 ON event (deleted, major) the EXPLAIN QUERY PLAN result remains the same and the timing returns the same results. Also, I'm running the ANALYZE, EXPLAIN and EXPLAIN QUERY PLAN commands through an external tool which uses SQLite.dll version 3.8.2. Thanks, Pavel. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
