in experimenting with indexes I found that if you create an index on (a, b)
and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index,
However, if you write SELECT * FROM data WHERE a = 1, it won't use the
index. If you write SELECT * FROM data WHERE a = 1 AND B !=
some_impossible_value, it does use the index.
Is it not efficient to use an index, even if one of its values is not
Case in point, I have an app that allows searching across 4 of parameters,
and I am wondering if I could just use a single 4-valued index, searching
for != impossible_val for unconstrained parameters. Surely that would be a
better use of resources than indexes on all combinations?
And if that is indeed the case, would it not be good that sqlite
automatically checks multi-value indexes where it can't find a specific
sqlite-users mailing list