List:General Discussion« Previous MessageNext Message »
From:David Giragosian Date:December 28 2009 7:38pm
Subject:Re: Is anything ever equal to NULL?
View as plain text  
On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso <dante@stripped> wrote:

>
> Will anything ever be equal to NULL in a SELECT query?
>
>  SELECT *
>  FROM sometable
>  WHERE somecolumn = NULL;
>
> I have a real-life query like this:
>
>  SELECT *
>  FROM sometable
>  WHERE somecolumn = NULL OR somecolumn = 'abc';
>
> The 'sometable' contains about 40 million records and in this query, it
> appears that the where clause is doing a sequential scan of the table to
>  find a condition where 'somecolumn' = NULL.  Shouldn't the query parser be
> smart enough to rewrite the above query like this:
>
>  SELECT *
>  FROM sometable
>  WHERE FALSE OR somecolumn = 'abc';
>
> And therefor use the index I have on 'somecolumn'?  When I manually rewrite
> the query, I get the performance I expect but when I leave it as it was,
> it's 100 times slower.
>
> What's so special about NULL?


http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Should answer some of your questions, Dante.

Thread
Is anything ever equal to NULL?D. Dante Lorenso28 Dec
  • Re: Is anything ever equal to NULL?Michael Dykman28 Dec
  • Re: Is anything ever equal to NULL?David Giragosian28 Dec
    • Re: Is anything ever equal to NULL?Carsten Pedersen28 Dec
      • Re: Is anything ever equal to NULL?David Giragosian29 Dec
  • Re: Is anything ever equal to NULL?Martijn Tonies28 Dec
    • Re: Is anything ever equal to NULL?D. Dante Lorenso29 Dec
    • Re: Is anything ever equal to NULL?Joerg Bruehe29 Dec
  • Re: Is anything ever equal to NULL?Martijn Tonies29 Dec