The additional fields are not contained in the index idxPostsThreadUser, so 
SQLite is forced to read the original row instead of just the index.

Your query is searching the complete posts table, joining all the threads, tags 
and users together, and then discarding those without a matching tag.

Maybe you would be better off scanning the ThreadTags from the target tag, and 
then reconstructing the post from there.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von John Found
Gesendet: Montag, 05. Februar 2018 15:14
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to