On 2/5/18, John Found <johnfo...@asm32.info> wrote: > 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? >
SQLite prefers to scan the index rather than the original table, because the index is usually smaller (since it contains less data) and hence there is less I/O required to scan the whole thing. But the index only provides access to the id, threadid, and userid columns. If content of other columns is needed, then the whole table must be scanned instead. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
