Dennis, Thanks for the reply. The original schema was causing us way to many problems. We ended up going with the second schema and the last query I posted we managed to get it down to .058 secs instead of 2.9 secs. I do appreciate all the suggestions and assistance.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Wednesday, April 09, 2008 6:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Left Join help 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 This message is confidential to Prodea Systems, Inc unless otherwise indicated or apparent from its nature. This message is directed to the intended recipient only, who may be readily determined by the sender of this message and its contents. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient:(a)any dissemination or copying of this message is strictly prohibited; and(b)immediately notify the sender by return message and destroy any copies of this message in any form(electronic, paper or otherwise) that you have.The delivery of this message and its information is neither intended to be nor constitutes a disclosure or waiver of any trade secrets, intellectual property, attorney work product, or attorney-client communications. The authority of the individual sending this message to legally bind Prodea Systems is neither apparent nor implied,and must be independently verified. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
