Sqlite 3.6.10 Background ======== I have the following abbreviated case:
CREATE TABLE sequence_elements ( sequence_element_oid integer primary key autoincrement, sequence_element_name varchar, definition_parent varchar, instance_parent varchar, soft_deleted_char varchar default 'F' ) CREATE UNIQUE INDEX sequence_element_name_idx on sequence_elements (sequence_element_name, instance_parent) CREATE INDEX sequence_element_definition_parent_idx on sequence_elements (definition_parent) CREATE INDEX sequence_element_instance_parent_idx on sequence_elements (instance_parent) CREATE TABLE data_elements ( data_element_oid integer primary key autoincrement, data_element_name varchar, definition_parent varchar, attribute_group varchar, hint_one varchar, hint_two varchar, hint_three varchar, options varchar, read_only_behavior_oid integer, soft_deleted_char varchar default 'F' ) CREATE INDEX data_element_name_idx on data_elements (data_element_name) CREATE INDEX data_element_definition_parent_idx on data_elements (definition_parent) CREATE TABLE responses ( response_oid integer primary key autoincrement, response_name varchar, definition_parent varchar, instance_parent varchar, value varchar, prelisted_value varchar, override_behavior_oid integer, soft_deleted_char varchar default 'F' ) CREATE INDEX response_definition_parent_idx on responses (definition_parent) CREATE UNIQUE INDEX response_instance_parent_idx on responses (instance_parent, definition_parent) The following query generates this query plan: explain query plan select DISTINCT RESPONSES.RESPONSE_OID from DATA_ELEMENTS, RESPONSES, SEQUENCE_ELEMENTS where SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_NAME = :sequence_element_name and DATA_ELEMENTS.DATA_ELEMENT_NAME = :data_element_name and RESPONSES.instance_parent = SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and RESPONSES.definition_parent = DATA_ELEMENTS.DATA_ELEMENT_OID order from detail 0 0 TABLE DATA_ELEMENTS WITH INDEX data_element_name_idx 1 2 TABLE SEQUENCE_ELEMENTS WITH INDEX sequence_element_name_idx 2 1 TABLE RESPONSES Problem ====== Apparently, responses is unable to use any indices which results in an O(n) table scan for the final step in the query. These queries are autogenerated, so hand tweaking them will be difficult. I have also tried this with CREATE INDEX response_instance_parent_idx on responses (instance_parent) with no change in EXPLAIN QUERY PLAN output. Any recommendations? John Elrick Fenestra Technologies _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
