List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:March 10 2010 6:59pm
Subject:RE: Particular value or NULL
View as plain text  
>-----Original Message-----
>Sent: Wednesday, March 10, 2010 10:55 AM
>To: mysql@stripped
>Subject: Re: Particular value or NULL
>
>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.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com




>""Jerry Schwartz"" <jschwartz@stripped> escreveu na mensagem
>news:023301cac069$366afa00$a340ee00$@com...
>> Is there a better construct for the WHERE clause in a LEFT JOIN than
>>
>>
>>
>> WHERE (x = 17 OR x IS NULL)
>>
>>
>>
>> ?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jerry Schwartz
>>
>> The Infoshop by Global Information Incorporated
>>
>> 195 Farmington Ave.
>>
>> Farmington, CT 06032
>>
>>
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>>
>>
>> <http://www.the-infoshop.com> www.the-infoshop.com
>>
>>
>>
>>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>infoshop.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