NULL in compound keys - Optimization

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

NULL in compound keys - Optimization

Christian Smith
Hi,

I have the following schema:

create table fields (
  type text,
  instance text,
  name text,
  value text,
  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.

Christian

--
    /"\
    \ /    ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
     X                           - AGAINST MS ATTACHMENTS
    / \