Re: [sqlite] Left Join help

[Dennis Cote]

Andy Smith wrote:
> I have to get this down to < 1 sec. I have 50 queries that are all
> formatted similar.  We have created another schema where the container
> is split into smaller tables which might help in speeding this up.  From
> what I see the Left joins are killing the speed.
> 

I don't think that is your problem. You are doing a full table scan of a 
large table. The last four left joins are required no matter what, but 
they will be quick since they use the container primary key to get the 
required record directly.

You want to use the most specific test you can to eliminate as many 
records as possible early on. The problem is that your LIKE test can't 
use an index as given. See http://www.sqlite.org/optoverview.html for 
details. You need to make a few changes to your query to get it to use 
the index on the mediaitem title for the like test.

First remove the unnecessary escape clause from the like clause. The set 
the case_sensitive_like pragma on. Next drop the index mediaitem_type so 
that sqlite will use the title index instead. Finally rearrange the 
order of the a and b tables so the like test is applied first using the 
index.

     sqlite> drop index mediaitem_type;
     sqlite> pragma case_sensitive_like = 1;
     sqlite> explain query plan
        ...> SELECT
        ...>     a.id,
        ...>     a.title,
        ...>     a.type,
        ...>     a.dateAdded,
        ...>     a.url,
        ...>     a.path,
        ...>     a.containerID,
        ...>     a.mimeType,
        ...>     a.width,
        ...>     a.height,
        ...>     a.genreID,
        ...>     a.thumbnailID,
        ...>     a.releaseYearID,
        ...>     a.artistID,
        ...>     w.title AS containerName,
        ...>     x.title AS genreName,
        ...>     y.title AS releaseYearName,
        ...>     z.title AS artistName
        ...> FROM mediaitem AS b
        ...> LEFT JOIN mediaitem AS a ON b.containerID=a.containerID
        ...> LEFT JOIN container AS w ON w.id=a.containerID
        ...> LEFT JOIN container AS x ON x.id=a.genreID
        ...> LEFT JOIN container AS y ON y.id=a.releaseYearID
        ...> LEFT JOIN container AS z ON z.id=a.artistID
        ...> WHERE b.title LIKE 'Opus%' --remove the escape clause
        ...>     AND b.type=0
        ...>     AND a.type=1
        ...> ORDER BY a.title, a.id
        ...> LIMIT 0,9;
     0|0|TABLE mediaitem AS b WITH INDEX mediaitem_title
     1|1|TABLE mediaitem AS a WITH INDEX mediaitem_containerID
     2|2|TABLE container AS w USING PRIMARY KEY
     3|3|TABLE container AS x USING PRIMARY KEY
     4|4|TABLE container AS y USING PRIMARY KEY
     5|5|TABLE container AS z USING PRIMARY KEY

With these changes you will use the title index to quickly reduce the 
table to only those records that match the title. For each of these 
records it will check the type, then join the records with the same 
containerID using that index with another check for the required type. 
Finally it will select the indicated records from the container table 
based on the rows selected.

HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users