List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:August 28 2011 7:54am
Subject:Re: a lesson in query writing and (maybe) a bug report
View as plain text  
Hi,

On 28-8-2011 4:08, shawn wilson wrote:
> On Sat, Aug 27, 2011 at 17:33, Arthur Fuller<fuller.artful@stripped>  wrote:
>> I agree 110%. It is completely pointless to index a column with that amount
>> of NULLs. In practical fact I would go further: what is the point of a
>> NULLable column?

A NULL 'value' is special in most operations. It indicates that the 
value is undefined, unknown, uncertain. In this regard it's actually not 
a value.
SELECT 'Uncertain' = TRUE;
Result: 0
SELECT 'Uncertain' = FALSE;
Result: 1
SELECT 'Uncertain' = NULL;
Result: NULL

SELECT NULL = TRUE;
Result: NULL
SELECT NULL = FALSE;
Result: NULL
SELECT NULL = NULL;
Result: NULL

(Unfortunately someone decided to add the <=> operator:
SELECT NULL <=> NULL;
Result: 1
Even stranger is that it is documented as "NULL safe" !?!?)

The advantage to me for having NULL 'values' is that it is usually 
handled as a truly undefined value. (When you compare an undefined value 
with for example 2, the result cannot be TRUE or FALSE. The undefined 
value might be equal to 2, or might not be equal to 2. The result can 
only be undefined.)
To deal with NULL results inside expressions COALESCE() is a very useful 
function.

> how does null effect an index? i had always assumed that, since there
> is nothing there, that record wouldn't go into the index hence
> wouldn't be processed when utilizing the index.

MySQL can use NULL in indexes when executing a query. If there are not 
enough different values in a column (low cardinality) it might be faster 
to do a full table search instead of first reading the index and then 
having to go through the table anyway.

-- 
Kind regards / met vriendelijke groet,

Jigal van Hemert.
Thread
a lesson in query writing and (maybe) a bug reportDave Dyer27 Aug
Re: a lesson in query writing and (maybe) a bug reportJigal van Hemert27 Aug
  • Re: a lesson in query writing and (maybe) a bug reportArthur Fuller27 Aug
    • Re: a lesson in query writing and (maybe) a bug reportshawn wilson28 Aug
      • Re: a lesson in query writing and (maybe) a bug reportJigal van Hemert28 Aug
Re: a lesson in query writing and (maybe) a bug reportMichael Dykman28 Aug