Of course, the correlated version returns all books, not just the ones that have at least one author, were read at least once, and have at least one genre.
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Friday, 15 March, 2019 14:44 >To: SQLite mailing list >Subject: Re: [sqlite] Query planner: Scanning subqueries vs using >automatic covering index > > >The current tip of trunk produced the same results demonstrated by >Niklas in his original post for his original testcase for me. > >I would have written the query as a simple query with correlated >subqueries to get the concatenated data as it is a "more natural" >declaration of what is wanted (in my opinion anyway), rather than re- >writing into joins. > > select BookID, > Title, > ( > SELECT group_concat(ltrim("First Name" || ' ' || "Last >Name"),',') > FROM AuthorBooks JOIN Authors USING (AuthorID) > WHERE BookID == Books.BookID > ) as "Author(s)", > ( > SELECT group_concat("Date read",', ') > FROM DatesRead > WHERE BookID == Books.BookID > ) as "Date(s)", > ( > SELECT group_concat(Genre,', ') > FROM BookGenres JOIN Genres USING (GenreID) > WHERE BookID == Books.BookID > ) AS "Genre(s)" > FROM Books >ORDER BY BookID; > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > >>-----Original Message----- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of niklas >>Sent: Friday, 15 March, 2019 01:36 >>To: sqlite-users@mailinglists.sqlite.org >>Subject: [sqlite] Query planner: Scanning subqueries vs using >>automatic covering index >> >>I recently noticed some very slow queries for my sqlite book >>database. >>Turns out that it happened due to the query planner decided to scan >>sub-queries instead of using an automatic covering index to search >>them. >> >>The database contains about 3000 entries and with two subqueries it >>took a noticable time to run, with three subqueries I had to give up >>and >>abort to save the CPU from overheating. >> >>Using indexes only take milliseconds in all cases. >> >>Dropping analyze (sqlite_stat1) information from the database made >>the problem go away, i.e. made sqlite use index instead of scanning. >>As did adding a few more columns to the ORDER BY part. >> >>These sql statements should be enough to demonstrate the problem. >>I am using sqlite 3.25.1 >> >>********** >>create.txt >>********** >> >>BEGIN; >> >>CREATE TABLE "Authors" ( >> "AuthorID" INTEGER PRIMARY KEY, >> "Last Name" TEXT NOT NULL, >> "First Name" TEXT NOT NULL); >> >>CREATE TABLE "Books" ( >> "BookID" INTEGER PRIMARY KEY, >> "Title" TEXT NOT NULL); >> >>CREATE TABLE "Genres"( >> "GenreID" INTEGER PRIMARY KEY, >> "Genre" TEXT UNIQUE NOT NULL); >> >>CREATE TABLE "DatesRead"( >> "BookID" INTEGER, >> "Date Read" TEXT, >> PRIMARY KEY("BookID", "Date Read"), >> FOREIGN KEY(BookID) REFERENCES Books(BookID)) WITHOUT ROWID; >> >>CREATE TABLE "AuthorBooks"( >> "BookID" INTEGER, >> "AuthorID" INTEGER, >> PRIMARY KEY("BookID", "AuthorID" ), >> FOREIGN KEY(BookID) REFERENCES Books(BookID), >> FOREIGN KEY(AuthorID) REFERENCES Authors(AuthorID)) WITHOUT >>ROWID; >> >>CREATE TABLE "BookGenres"( >> "BookID" INTEGER, >> "GenreID" INTEGER, >> PRIMARY KEY("BookID", "GenreID" ), >> FOREIGN KEY(BookID) REFERENCES Books(BookID), >> FOREIGN KEY(GenreID) REFERENCES Genres(GenreID)) WITHOUT ROWID; >> >>ANALYZE; >>INSERT INTO sqlite_stat1 VALUES('BookGenres','BookGenres','3190 2 >>1'); >>INSERT INTO sqlite_stat1 VALUES('DatesRead','DatesRead','3047 2 1'); >>INSERT INTO sqlite_stat1 VALUES('AuthorBooks','AuthorBooks','3549 2 >>1'); >>INSERT INTO sqlite_stat1 VALUES('Authors',NULL,'1329'); >>INSERT INTO sqlite_stat1 VALUES('Books',NULL,'2978'); >>INSERT INTO sqlite_stat1 >>VALUES('Genres','sqlite_autoindex_Genres_1','112 >>1'); >> >>COMMIT; >> >>************ >>queries.txt >>************ >> >>select "--------------------"; >>select "order by bookid only"; >>select "--------------------"; >> >>EXPLAIN QUERY PLAN >>SELECT BookID,Title,"Author(s)","Date(s)","Genre(s)" >>FROM Books >>JOIN (SELECT BookID, group_concat(ltrim("First Name"||' '||"Last >>Name"),', >>') AS 'Author(s)' FROM Books JOIN AuthorBooks USING(BookID) JOIN >>Authors >>USING(AuthorID) GROUP BY BookID) USING(BookID) >>JOIN (SELECT BookID, group_concat("Date read",', ') AS 'Date(s)' >FROM >>Books >>JOIN DatesRead USING(BookID) GROUP BY BookID) USING(BookID) >>JOIN (SELECT BookID, group_concat(Genre,', ') AS 'Genre(s)' FROM >>Books JOIN >>BookGenres USING(BookID) JOIN Genres USING(GenreID) GROUP BY BookID) >>USING(BookID) >>ORDER BY BookID; >> >>select "-------------------------------------------"; >>select "order by bookid and some other fields too"; >>select "------------------------------------------"; >> >>EXPLAIN QUERY PLAN >>SELECT BookID,Title,"Author(s)","Date(s)","Genre(s)" >>FROM Books >>JOIN (SELECT BookID, group_concat(ltrim("First Name"||' '||"Last >>Name"),', >>') AS 'Author(s)' FROM Books JOIN AuthorBooks USING(BookID) JOIN >>Authors >>USING(AuthorID) GROUP BY BookID) USING(BookID) >>JOIN (SELECT BookID, group_concat("Date read",', ') AS 'Date(s)' >FROM >>Books >>JOIN DatesRead USING(BookID) GROUP BY BookID) USING(BookID) >>JOIN (SELECT BookID, group_concat(Genre,', ') AS 'Genre(s)' FROM >>Books JOIN >>BookGenres USING(BookID) JOIN Genres USING(GenreID) GROUP BY BookID) >>USING(BookID) >>ORDER BY BookID, Title, "Author(s)", "Date(s)"; >> >> >>************ >>Test run: >>************ >> >>del test.db >>type create.txt | sqlite3 test.db >>type queries.txt | sqlite3 test.db >> >> >>************ >>Output: >>************ >> >>-------------------- >>order by bookid only >>-------------------- >>QUERY PLAN >>|--MATERIALIZE 1 >>| |--SCAN TABLE Books >>| |--SEARCH TABLE AuthorBooks USING PRIMARY KEY (BookID=?) >>| `--SEARCH TABLE Authors USING INTEGER PRIMARY KEY (rowid=?) >>|--MATERIALIZE 2 >>| |--SCAN TABLE Books >>| `--SEARCH TABLE DatesRead USING PRIMARY KEY (BookID=?) >>|--MATERIALIZE 3 >>| |--SCAN TABLE Books >>| |--SEARCH TABLE BookGenres USING PRIMARY KEY (BookID=?) >>| `--SEARCH TABLE Genres USING INTEGER PRIMARY KEY (rowid=?) >>|--SCAN SUBQUERY 1 >>|--SCAN SUBQUERY 2 >>|--SCAN SUBQUERY 3 >>|--SEARCH TABLE Books USING INTEGER PRIMARY KEY (rowid=?) >>`--USE TEMP B-TREE FOR ORDER BY >>------------------------------------------- >>order by bookid and some other fields too >>------------------------------------------ >>QUERY PLAN >>|--MATERIALIZE 1 >>| |--SCAN TABLE Books >>| |--SEARCH TABLE AuthorBooks USING PRIMARY KEY (BookID=?) >>| `--SEARCH TABLE Authors USING INTEGER PRIMARY KEY (rowid=?) >>|--MATERIALIZE 2 >>| |--SCAN TABLE Books >>| `--SEARCH TABLE DatesRead USING PRIMARY KEY (BookID=?) >>|--MATERIALIZE 3 >>| |--SCAN TABLE Books >>| |--SEARCH TABLE BookGenres USING PRIMARY KEY (BookID=?) >>| `--SEARCH TABLE Genres USING INTEGER PRIMARY KEY (rowid=?) >>|--SCAN SUBQUERY 1 >>|--SEARCH TABLE Books USING INTEGER PRIMARY KEY (rowid=?) >>|--SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX (BookID=?) >>|--SEARCH SUBQUERY 3 USING AUTOMATIC COVERING INDEX (BookID=?) >>`--USE TEMP B-TREE FOR ORDER BY >> >> >>As can be seen the first query (when analyze information is in >place) >>will use three nested scans as opposed to the second query with more >>order by columns that instead uses indexes to search. >> >>Now, might this be a weakness in the query planner, or is there some >>precautions I could/should always take to avoid the scanning? >> >> >> >> >> >> >>-- >>Sent from: http://sqlite.1065341.n5.nabble.com/ >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
