I am trying to track down an issue that I MIGHT have with the following query. The explain query is a bit complex and I cannot tell if it is OK or if there are issues. Here it is:
sqlite> EXPLAIN QUERY PLAN ...> SELECT DISTINCT f1.FolderId, f1.ImageId, ...> (SELECT MIN(f2.instertedon) FROM Favorite f2 WHERE f2.FolderId = f1.FolderId AND f2.ImageId = f1.ImageId) timeMarker ...> FROM Favorite f1 ...> JOIN Customer c ON f1.CustomerId = c.CustomerId ...> WHERE f1.Selected = 1 AND c.IsInSlideShow = 1 AND timeMarker > julianday(2455676.2469821414) ...> ORDER BY timeMarker; 0|0|1|SEARCH TABLE Customer AS c USING COVERING INDEX Customer_IsInSlideShow_index (IsInSlideShow=?) (~10 rows) 0|1|0|SEARCH TABLE Favorite AS f1 USING INDEX Favorite_CustomerId_Selected_Status_index (CustomerId=? AND Selected=?) (~4 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE Favorite AS f2 USING COVERING INDEX Favorite_FolderId_ImageId_instertedon_index (FolderId=? AND ImageId=?) (~1 rows) 0|0|0|USE TEMP B-TREE FOR DISTINCT 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE Favorite AS f2 USING COVERING INDEX Favorite_FolderId_ImageId_instertedon_index (FolderId=? AND ImageId=?) (~1 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 3|0|0|SEARCH TABLE Favorite AS f2 USING COVERING INDEX Favorite_FolderId_ImageId_instertedon_index (FolderId=? AND ImageId=?) (~1 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4 4|0|0|SEARCH TABLE Favorite AS f2 USING COVERING INDEX Favorite_FolderId_ImageId_instertedon_index (FolderId=? AND ImageId=?) (~1 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY CREATE TABLE Customer ( CustomerId INTEGER NOT NULL, Customer_Detail_Id INTEGER, IsInSlideShow INTEGER NOT NULL, Username VARCHAR(50) NOT NULL, Password VARCHAR(50), IsDeleted INTEGER NOT NULL DEFAULT 0, CopiedTo INTEGER, insertedby VARCHAR(50) NOT NULL, instertedon TIMESTAMP NOT NULL, updatedby VARCHAR(50) NOT NULL, updatedon TIMESTAMP NOT NULL, PRIMARY KEY (CustomerId), UNIQUE (username)) CREATE TABLE Favorite ( FavoriteId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, CustomerId INTEGER NOT NULL, FolderId VARCHAR(50) NOT NULL, ImageId VARCHAR(260) NOT NULL, Selected INTEGER DEFAULT 1 NOT NULL, Status INTEGER, insertedby VARCHAR(50) NOT NULL, instertedon TIMESTAMP NOT NULL, updatedby VARCHAR(50) NOT NULL, updatedon TIMESTAMP NOT NULL, UNIQUE (CustomerId, FolderId, ImageId)) CREATE INDEX Customer_IsInSlideShow_index ON Customer (IsInSlideShow) CREATE INDEX Favorite_CustomerId_Selected_Status_index ON Favorite (CustomerId, Selected, Status) CREATE INDEX Favorite_CustomerId_FolderId_ImageId_index ON Favorite (CustomerId, FolderId, ImageId) The system is a photo presentation system used at live events. The goal of this query is to get the next image in the slide show. Customers come up to the system, browse images and then add some images to their Favorite's (aka Favorite table). The Customer table has a column IsInSlideShow, that if 0, any images that customer tagged as a favorite should NOT be in the slide show. It is possible for multiple Customers to add the same image to their personal Favorite. The other goal of this query is to only show the first instance of an image in the Favorite table, which is why there is the inner select for the min(insertedon), this is used to eliminate all but the first instance in the favorites table. Two more thing, the insertedon (timeMarker) is returned to the viewing station along with the image, so when the viewing station queries for the next image, it sends the last timeMaker. The query is NOT limited to 1 because the backend does check for the physical presents of the image before returning it to the viewing station, if it is NOT physically there, it gets the next record until the image IS there. So, is the Query OK or are there things I can do to improve it? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
