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