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 <[email protected]>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
