Ok, thank you very much for everybody's help.
----- Original Message ----- From: Shawn Wagner <shawnw.mob...@gmail.com> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Sent: Wednesday, February 13, 2019, 13:58:19 Subject: [sqlite] Tips for index creation 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
