D. Richard Hipp wrote: > On Apr 1, 2009, at 2:00 PM, John Elrick wrote: > >> 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 >> > > > The index is not being used on the RESPONSES table because your WHERE > clause constraint is comparing a TEXT column (instance_parent) against > an INTEGER column (sequence_element_oid). The rules of SQLite are > that this requires a NUMERIC comparison, but the index is constructed > using a TEXT collation and so the index cannot be used. > > Various workarounds: > > (1) redefine RESPONSES.INSTANCE_PARENT to be type INTEGER. (Do the > same with RESPONSES.definition_parent). > > (2) Add a "+" sign in front of sequence_element_oid in the where clause: > > ... instance_parent = +sequence_element_oid... > > This will force the RHS of the expression to be an expression rather > than a column name. That will force the use of TEXT collating for the > comparison, and thus make the index usable. > > (3) Case the integer on the RHS to text: > > ... instance_parent = CASE(seqence_element_oid AS varchar) ... > > Seems like (1) is probably the right fix, but any of these three will > work.
Good heavens Richard! I didn't notice. There is no reason they can't be integers and I honestly thought they were. Thanks very much. John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
