Hi, have I missed something obvious or is my explanation that bad? I would really like to promote sqlite in our group but if joins are so slow that won't happen. Any advice?
Thorsten On Monday 03 January 2005 17:14, Thorsten Schuett wrote: > 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
