Received: from oxmail.ox.ac.uk (oxmail3.ox.ac.uk [163.1.2.9]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA40125 for ; Mon, 13 Dec 1999 05:41:02 -0500 (EST) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from ermine.ox.ac.uk ([163.1.2.13]) by oxmail.ox.ac.uk with esmtp (Exim 2.10 #1) id 11xSuN-00074q-00; Mon, 13 Dec 1999 10:40:47 +0000 Received: from ermine.ox.ac.uk ([163.1.2.13] helo=moraypc ident=root) by ermine.ox.ac.uk with smtp (Exim 2.12 #1) id 11xSuN-0005uj-00; Mon, 13 Dec 1999 10:40:47 +0000 Message-ID: <005d01bf4556$82cb0370$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: "Tom Lane" Cc: References: <7971.945062060@sss.pgh.pa.us> Subject: Re: [SQL] how to tell the difference between empty field and null field Date: Mon, 13 Dec 1999 10:40:44 -0000 Organization: Oxford University MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Far be it from me to defend Microsoft, but not all sloppiness is down to Microsoft. All Microsoft databases support IS NULL, and indeed advocate it: from the Access help file... Searching for Null values or zero-length strings ---------------------------------------------------------------- If you're using a query to search for Null values or zero-length strings, type Is Null into the Criteria cell to search for Null values, or type two double quotation marks (" ") into the Criteria cell to search for zero-length strings (don't type a space between the quotation marks). Yours, Moray ---------------------------------------------------------------------- ---------------- Moray.McConnachie@computing-services.oxford.ac.uk ----- Original Message ----- From: Tom Lane To: Alex Howansky Cc: Sent: Monday, December 13, 1999 5:14 AM Subject: Re: [SQL] how to tell the difference between empty field and null field > Alex Howansky 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 > > ************ > >