On 2/13/19, 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?
The preferred index here is: CREATE INDEX x1 ON metadata(id1,id2,id3); > > 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?) If you add "parameter" and "value" to the index, like this: CREATE INDEX x1 ON metadata(id1,id2,id3,parameter,value); that will make the index into a "covering index" for the queries you show above, and a covering index does usually run faster. However, it will also make your index take up more space on disk. So you need to decide which is more important for you. The cool think about SQL is that you can change this on-the-fly (by dropping and rebuilding the index) to try it out, without having to make any modifications to your application - indeed without having to recompile your application, or even to restart your application. You can run experiments with various index configurations to see which one works best for you. -- 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
