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
