On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk <csteen...@gmail.com> wrote:
> Ah, sorry about the attachments, you can find the files here: > https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb > > And yes, as the final part of the DB creation I do run ANALYZE. And I do > think the indexes are correct for the query. Wild, random stab in the dark… I suppose this is an equivalent query: select metadata.attribute_value_id as metadata_value_id, object_characteristics.attribute_value_id as object_type_value_id, sum( case when metrics.color = 1 and metrics.quality > 0 then 1 end ) as pass, count( metrics.id ) as total from metadata join frames on frames.id between metadata.start_frame_id and metadata.stop_frame_id join metrics on metrics.frame_id = frames.id join object_characteristics on object_characteristics.object_id = metrics.object_id where frames.session_frame_id > 12 and frames.ticks > 10 and exists ( select 1 from attribute_types where attribute_types.id = metadata.attribute_type_id and attribute_types.type = 'Metadata Type' ) and exists ( select 1 from attribute_types where attribute_types.id = object_characteristics.attribute_type_id and attribute_types.type = 'Object Type' ) group by metadata.attribute_value_id, object_characteristics.attribute_value_id explain query plan: 0|0|0|SCAN TABLE metadata USING COVERING INDEX metadata_type_value_idx (~500000 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE attribute_types USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|1|2|SEARCH TABLE metrics USING INDEX metrics_idx (frame_id>? AND frame_id<?) (~62500 rows) 0|2|1|SEARCH TABLE frames USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|3|3|SEARCH TABLE object_characteristics USING INDEX sqlite_autoindex_object_characteristics_1 (object_id=?) (~5 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE attribute_types USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY (n.b.: metrics_idx on metrics( frame_id, color, quality ) is most likely useless and only confuses things, I would suggest dropping it) Does that run in less than 6 days? Hint: don't wait that long :) P.S. On June 28th, you said your query was running in "15 minutes which I am OK with". On Linux at least. What happened in the meantime? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
