Just to update, we're attempting to move to a 3.8.0 snapshot, and we've run into another possibly pathological case you might want to be aware of (same schema). The query is:
select count(*) from metadata_items as leaves join metadata_items as parents on leaves.parent_id=parents.id left join metadata_item_settings on metadata_item_settings.guid = leaves.guid where (parents.parent_id=516104 or parents.id=516104) and metadata_item_settings.view_count>0; The old query plan was (around 200ms): 0|0|1|SEARCH TABLE metadata_items AS parents USING COVERING INDEX index_metadata_items_on_parent_id (parent_id=?) (~10 rows) 0|0|1|SEARCH TABLE metadata_items AS parents USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|1|0|SEARCH TABLE metadata_items AS leaves USING INDEX index_metadata_items_on_parent_id (parent_id=?) (~10 rows) 0|2|2|SEARCH TABLE metadata_item_settings USING INDEX index_metadata_item_settings_on_guid (guid=?) (~2 rows) But the new one generated by the NGQP is (around 7 seconds): 0|0|0|SCAN TABLE metadata_items AS leaves 0|1|1|SEARCH TABLE metadata_items AS parents USING INTEGER PRIMARY KEY (rowid=?) 0|2|2|SEARCH TABLE metadata_item_settings USING INDEX index_metadata_item_settings_on_guid (guid=?) The "culprit" appears to be the "(parents.parent_id=516104 or parents.id=516104)" part. If I break that up there is no table scan. Let me know if you need any more data, hopefully this is useful feedback. At this point we may return to 3.7.14, which appears to be the last version which executes all these queries quickly. -elan > I get times like this: > > 3.7.14: 0.120s > 3.7.17: 14.998s > 3.8.0: 0.064s > > So on my runs, at least, it appears that 3.8.0 is about 2x faster than > 3.7.14. But you say that 3.8.0 is slower for you? What are your query > times? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
