On Mon, 5 Feb 2018 15:08:33 +0000 Hick Gunter <h...@scigames.at> wrote:
> I think you are optimizing the performance of a conceptually inefficient > query. > > If you are looking for a recipe that contains apples, do you read the entire > cook book, checking each recipe for apples? Maybe it is much more efficient > to look up apples in the index of ingredients and retrieve only the recipes > that actually contain them. You are definitely right, but the things are a little bit more complex. The query I asked for is simplified in order to make the question more clear. It is part of a complex search, looking simultaneously in several fields: fts5 search (removed for simplicity), T.Caption, TT.Tag and U.nick fields. I am trying to estimate how exactly to handle all these possible combinations and whether it is possible to be done with one fixed query or need specially synthesized query for every particular case. > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von John Found > Gesendet: Montag, 05. Februar 2018 15:55 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] Re: [sqlite] Optimization - don't understand. > > It is clear now. But should I define an index that contains all fields used > in the query? > > Something like: > > create index idxPostsComplex on posts(threadid, userid, Content, > postTime, ReadCount); > > Actually I tried and the query uses this index without problems (and the > performance seems to be good). > > But what are the disadvantages of such approach? (except the bigger database > size, of course) > > On Mon, 5 Feb 2018 09:24:51 -0500 > Richard Hipp <d...@sqlite.org> wrote: > > > 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 > > > -- > 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 -- 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
