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. D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users