Re: [SQL] how to tell the difference between empty field and null field

Alex Howansky <alex@wankwood.com> writes:
> select * from users where domain = '' or domain = null;

OK, that'll work, but if you'll pardon a nitpick: "= NULL" is not
standard, it is Microsoft brain damage.  "IS NULL" is standard.

> Here's my question: if I have a zillion records in this table, and
> it's indexed by user+domain, how can I run this query without losing
> the benefit of the index?

An index on (user, domain) is perfectly useless for the above query,
because the user field isn't mentioned anywhere in the query.  An index
on domain alone could be used, though, and should be pretty effective.
(We do have some performance problems if you get into dozens of OR
terms, but for just a couple, no sweat.)

As a rule of thumb, multi-column indexes are quite inflexible, and
you will not find that they are worth their cost of upkeep unless
you know that you have a specific kind of query you use a *lot* that
can exploit the multi-column index.  Not only that, but that the
heavily used query is significantly faster than it'd be if it only
had an index on the first column named in the multi-column index.
In most scenarios, the first column gives you most of the gain and
any extra columns are marginal.

In short, unless you've done careful analysis and testing, you should
not make an index on (user, domain) but two indexes on user and domain
separately.  The latter setup will be a lot more flexible in terms of
being reasonably quick for a variety of queries.
        regards, tom lane