Some useful reading: https://use-the-index-luke.com/
https://www.sqlite.org/queryplanner.html (and the pages it links to) There's also the .expert command in the sqlite shell: sqlite> .expert sqlite> SELECT ... FROM ...; will suggest indexes that will benefit a particular query. On Wed, Feb 13, 2019, 4:39 AM Thomas Kurz <sqlite.2...@t-net.ruhr wrote: > Hello, > > I apologize right at the beginning, because this is a real noob question. > But I don't have much experience with performance optimization and indexes, > so I'm hoping for some useful hints what indexes to create. > > I have queries like this: > > SELECT parameter, value FROM metadata WHERE id1=a AND id2 IS NULL and id3 > IS NULL > -or- > SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3 IS NULL > -or- > SELECT parameter, value FROM metadata WHERE id1=a AND id2=b and id3=c > > Do I have to create three indexes, one for each column id1, id2, id3? Or > is it better or even necessary to create only one index covering all three > id columns? > > Do I need indexes for the parameter and value columns as well, and under > which circumstances? (Only if I want to use a SELECT ... WHERE parameter = > xy?) > > Kind regards, > Thomas > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
