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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
