Hi,
I am currently porting an application from a custom db backend to sqlite and
have some difficulties understanding how sqlite uses indices in joins. I have
several tables (MDCATTR_*) which have two columns mdc_oid and mdc_value. I
have for testing created indices on mdc_oid, mdc_value and both combinations
of both columns. The queries I'm interested in, look like the following:
SELECT objs.mdc_oid FROM
MDCATTR_MDC_TYPE AS type, MDCATTR_NAME AS name,
MDCATTR_PARENT_NAME AS parent, MDCOBJECTS AS objs
WHERE (
(name.mdc_oid=type.mdc_oid) AND (parent.mdc_oid=name.mdc_oid) AND
(objs.mdc_oid=parent.mdc_oid) AND (type.mdc_value='MDC_HFN') AND
(parent.mdc_value='/tmp/file-test') AND (name.mdc_value='foo0.0'));
I am basically joining several tables on the mdc_oid and selecting different
values for different tables. The assembler of the vm roughly looks like that:
foreach row_type in type where row_type.mdc_value="MDC_HFN"
foreach row_parent in parent where row_parent.mdc_value="/tmp/file-test"
if(row_type.mdc_oid != row_parent.oid)
continue;
foreach row_name in name where row_name.mdc_value="foo0.0"
if(row_type.mdc_oid != row_name.oid)
continue;
call callback;
where the expressions in the "where"-part are used for index-lookups. The
question I am having is as follows:
Can I make sqlite use a more complex index for the joins? E.g.:
foreach row_type in type where row_type.mdc_value="MDC_HFN"
foreach row_parent in parent where row_parent.mdc_value="/tmp/file-test" and
row_parent.mdc_oid = row_type.mdc_oid
[...]
So that the if-statements are converted into index-lookups?
Thanks in advance,
Thorsten
