Re: [sqlite] Indices and Joins
On Wednesday 05 January 2005 13:05, D. Richard Hipp wrote: > Thorsten Schuett wrote: > > have I missed something obvious or is my explanation that bad? > > Your original posting was lengthy and dense. I doubt many people took > the time to read it. Thanks for the kind paraphrase of "hard to read". ;-) > > 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? > > Create indices as follows: > > CREATE INDEX idx1 ON type(mdc_value); > CREATE INDEX idx2 ON parent(mdc_value, mdc_oid); Damnit. I create such indices but not on all tables. Thanks a lot, Thorsten
Re: [sqlite] Indices and Joins
Thorsten Schuett wrote: have I missed something obvious or is my explanation that bad? Your original posting was lengthy and dense. I doubt many people took the time to read it. 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? Create indices as follows: CREATE INDEX idx1 ON type(mdc_value); CREATE INDEX idx2 ON parent(mdc_value, mdc_oid); The second index could also be this: CREATE INDEX idx2 ON parent(mdc_oid, mdc_value);\ See http://www.sqlite.org/php2004/page-001.html and especially http://www.sqlite.org/php2004/page-058.html and the surrounding pages. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Indices and Joins
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
[sqlite] Indices and Joins
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