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
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
