Andy Smith wrote:
> I have quiet a few queries similar to this doing multiple Left Joins and
> they run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
>
Andy,
You didn't show your table and index definitions, so it's hard to be
sure about what would be best.
The following trace shows what I think your tables should look like, and
a couple of indexes that will help for a slightly rearranged version of
your query. The query plan uses the indexes to locate the subset of the
records with type=0, then checks their title, next it uses the other
index to find the matching records and checks that their type=1. These
records should lead directly to the required container records using the
primary key on that table. I would suspect this is reasonably fast. Let
me know if it helps.
HTH
Dennis Cote
SQLite version 3.5.7
Enter ".help" for instructions
sqlite>
sqlite> create table container (
...> id integer primary key,
...> title text
...> );
sqlite>
sqlite> create table mediaitem (
...> id integer primary key,
...> title text,
...> type integer,
...> containerID integer references container,
...> genreID integer references container,
...> thumbnailID integer references container,
...> releaseYearID integer references container,
...> artistID integer references container,
...> dateAdded,
...> url,
...> path,
...> mimeType,
...> width,
...> height
...> );
sqlite>
sqlite> create index media_type on mediaitem(type);
sqlite> create index media_container on mediaitem(containerID);
sqlite>
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%' ESCAPE '\'
...> 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 media_type
1|1|TABLE mediaitem AS a WITH INDEX media_container
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
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
