Is P.id an integer primary key? If so then it can get it from any index. Since the only field it needs from P is id, it can use an index which isn't defined on id. This can make it quicker, especially if P has a lot of fields bloating its size. Once you include the other fields of P in the query then it has to go to the main table anyway to get those other fields and decides to do a full scan.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John Found Sent: Monday, February 05, 2018 9:14 AM To: SQLite mailing list Subject: [sqlite] Optimization - don't understand. The following query: explain query plan select U.nick, U.id, U.av_time, T.Caption, P.id, -- P.ReadCount, -- P.Content, -- P.postTime,l T.Caption from Posts P left join Threads T on P.threadID = T.id left join ThreadTags TT on TT.threadID = T.id left join Users U on P.userID = U.id where TT.Tag = ?1; ...returns: 0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?) 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX idxThreadTagsUnique (ThreadID=? AND Tag=?) 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?) But uncommenting any of the commented fields, turns the result into: 0 0 0 SCAN TABLE Posts AS P 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?) 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX idxThreadTagsUnique (ThreadID=? AND Tag=?) 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?) ... and significantly degrades the performance. The index idxPostsThreadUser is defined following way: create index idxPostsThreadUser on Posts(threadid, userid); IMHO, the change of the selected columns should not affect the query plan, but maybe I am wrong somehow. What I am missing? -- http://fresh.flatassembler.net http://asm32.info John Found <johnfo...@asm32.info> _______________________________________________ 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
