>>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
