Andy Smith wrote: > > With all the changes that got me down to 4.8 seconds. Better.
Can you post the exact query that ran in 4.8 seconds? Did you set case_sensitive_like on before you ran the query? > We are > also trying a schema in which we break up the container so it's a bit > smaller. Now my other 54 queries run sub .5 secs except 1. Which is a > variant of the one I have asked for help on. Here is the new version on > a schema based on the container table being split apart. > > explain query plan SELECT a.id, a.title, a.dateAdded, a.url, a.path, > a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID, > a.releaseYearID, a.artistID, (SELECT w.title AS containerName FROM > allAlbums AS w WHERE w.id=a.containerID), (SELECT x.title AS genreName > FROM allgenres AS x WHERE x.id=a.genreID), (SELECT y.title AS > releaseYearName FROM allreleaseYears AS y WHERE y.id=a.releaseYearID), > (SELECT z.title AS artistName FROM allartists AS z WHERE > z.id=a.artistID) FROM mediaImage AS a INNER JOIN mediaAudio AS b ON > b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER > BY a.title, a.id LIMIT 0,9; > > 0|0|TABLE mediaImage AS a WITH INDEX mediaImage_title ORDER BY > 1|1|TABLE mediaAudio AS b WITH INDEX mediaAudio_containerID > 0|0|TABLE allAlbums AS w USING PRIMARY KEY > 0|0|TABLE allgenres AS x USING PRIMARY KEY > 0|0|TABLE allreleaseYears AS y USING PRIMARY KEY > 0|0|TABLE allartists AS z USING PRIMARY KEY > > This runs in about 2.9 seconds. Still way slower than needed. I am > really debating whether this can be sped up to meet the < 1 second goal. > This query has the same issues as the first one. The escape clause on the like invalidates the use of an index. The order of the tables a and b is sub optimal. The first scan is a full table scan using the title index for the ordering only. It will be faster swap the order the tables are scanned to eliminate all the records that don't have the correct title first. Instead of this: FROM mediaImage AS a INNER JOIN mediaAudio AS b ON b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9; try this: FROM mediaAudio AS b JOIN mediaImage AS a ON b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ORDER BY a.title, a.id LIMIT 0,9; Make sure you turn on case_sensitive_like before executing the query. The title index will be used to select the b table records based on the like condition, then the a table records will be matched by the containerID. This query (and the first one) will have to sort the result records instead of using the title index for the ordering, but there will hopefully be few records to sort, so it should be fast. How many records does this query return? HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
