List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 10 2010 8:24pm
Subject:Re: Particular value or NULL
View as plain text  
>>Maybe:
>>
>>WHERE coalesce(x, 17) = 17
>>
>>
> [JS] Interesting suggestion, but
>
> us-gii >select benchmark(10000000,(7=7 or null is null));
> +-------------------------------------------+
> | benchmark(10000000,(7=7 or null is null)) |
> +-------------------------------------------+
> |                                         0 |
> +-------------------------------------------+
> 1 row in set (0.34 sec)
>
> us-gii >select benchmark(100000000,coalesce(null,7));
> +---------------------------------------+
> | benchmark(100000000,coalesce(null,7)) |
> +---------------------------------------+
> |                                     0 |
> +---------------------------------------+
> 1 row in set (2.61 sec)
>
> It looks like COALESCE() is slower. Of course this isn't anything like a 
> real
> test. Among other things, I have no idea how well or poorly the optimizer,
> query cache, etc. handle it. I don't know how much magic there is in the
> BENCHMARK() function, either. I would hope that the server would know that 
> the
> expression needs to be evaluated over and over again from scratch.

WHERE COALESCE(<column>, ... ) = ...

cannot use an index, I guess, which is why the comparison is slow.

If NULLs are in indices, IS NULL could be index optimized as well.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com 

Thread
Particular value or NULLJerry Schwartz10 Mar
  • Re: Particular value or NULLjoao10 Mar
    • RE: Particular value or NULLJerry Schwartz10 Mar
      • Re: Particular value or NULLJoerg Bruehe11 Mar
  • Re: Particular value or NULLjoao10 Mar
    • RE: Particular value or NULLJerry Schwartz11 Mar
  • Re: Particular value or NULLMartijn Tonies10 Mar