Re: [sqlite] Indices and Joins

[Thorsten Schuett]

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]

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

[Thorsten Schuett]

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