create table fields (
primary key (type,instance,name) on conflict replace
What I'm trying to do is read all rows of some type, that are not
associated with an instance, to form the basis of default value processing
for new instances of that type:
SELECT name, value FROM fields WHERE type = '<type>' AND instance IS NULL;
I'd expect this to use the compound index to check for NULL instances, but
it does not. It instead opens the index, and iterates through all rows
with the given type. Thus, I'm searching all rows associated with any
instance just to find rows not associated with any instance. This is with
2.8.x, but I've just checked and 3.2.1 also shows this behaviour.
Any ideas on how I could optimize this query? For the time being, I'll
just use an empty string instead to indicate no instance, but I'd prefer a
NULL as it's more intuitive.
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS