On 2019/02/13 2:39 PM, Thomas Kurz 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 quickest will be a covering Index on a, b and c (in that specific
order).
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?)
Depends, the covering index on a, b, c, parameter, value will be very
fast although somewhat larger (more space needed), but if the table only
really contains those columns, the speed advantage will be negligible.
However, if the table contains many columns, let's say 40+ columns, or
perhaps other columns of which some of the values are rather wide (long
strings, blobs, etc.) then singling out the 5 mentioned columns into a
covering Index will definitely have a significant advantage (especially
when row-count grows large).
A covering Index is essentially a copy of the table singling out the
pertinent data, using up extra space but gaining faster access as a
result. The decision is always weighed on space cost vs. speed gain,
and sometimes it's hard to find a definite answer without testing on a
DB with enough real-world data.
So I guess the answer is: Try it, if it works better, keep it!
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
