[Digikam-devel] extragear/graphics/digikam/libs/database

[Andi Clemens]

Ok one last note here: In the link above it is explained quite well I think.
So LEFT JOIN can generate performance issues, especially when used nested.
The thread also is talking about the EXPLAIN command, which I was talking 
about before.
It is used to see which instructions SQL is doing behind the scenes.
LEFT JOINs will mostly generate more instructions, and sometimes performance 
loss is really visible (someone mentioned in the link that he dropped 
execution time from 600ms to 20ms or 30ms, which is quite good).

Andi

On Thursday 28 May 2009 21:05:51 Andi Clemens wrote:
> Ok here is some link, not the one I thought of, but it also says that LEFT
> JOIN can restrict ordering of the tables and therefore restrict
> optimization:
>
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg39455.html
>
> The
>
> a JOIN b ON a.x = b.x
>
> should be the same as
>
> a,b WHERE a.x = b.x
>
> in our case.
>
> Andi
>
> On Thursday 28 May 2009 20:58:02 Andi Clemens wrote:
> > I found only a quote now:
> >
> > "So when you use LEFT JOIN, that forces a particular ordering
> > of tables, and greatly restricts the query engines opportunities to
> > optimize.  So you should avoid using LEFT JOIN if you don't really
> > need it."
> >
> > Seems that LEFT JOIN will not be optimized like the normal table
> > connection by WHERE.
> >
> > Andi
> >
> > On Thursday 28 May 2009 20:54:24 Andi Clemens wrote:
> > > All I know is that LEFT JOIN seems to generate more internal SQL code,
> > > which might be a little bit slower.
> > > There is also some statement to see what SQL is doing internally, but I
> > > forgot it.
> > >
> > > I think I have a bookmark on this topic, I will attach it to this
> > > thread when I find it.
> > >
> > > I learned it this way, never used LEFT JOIN. But I don't think that it
> > > will make any difference in our case.
> > >
> > > Andi
> > >
> > > On Thursday 28 May 2009 20:47:08 Marcel Wiesweg wrote:
> > > > Thanks Andi. I am out of exercise with the database code ;-)
> > > > The query their is not old but just left over, I will remove it.
> > > > Btw I would have coded the statement like this:
> > > >
> > > > SELECT count() FROM Images
> > > > LEFT JOIN ImageInformation ON imageid == id
> > > > WHERE Images.status== 1  AND ImageInformation.format='JPG';
> > > >
> > > > Do you know pros and cons of either variant?
> > > >
> > > > Marcel
> > >
> > > _______________________________________________
> > > Digikam-devel mailing list
> > > Digikam-devel at kde.org
> > > https://mail.kde.org/mailman/listinfo/digikam-devel
> >
> > _______________________________________________
> > Digikam-devel mailing list
> > Digikam-devel at kde.org
> > https://mail.kde.org/mailman/listinfo/digikam-devel
>
> _______________________________________________
> Digikam-devel mailing list
> Digikam-devel at kde.org
> https://mail.kde.org/mailman/listinfo/digikam-devel