List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:March 11 2010 2:48pm
Subject:RE: Particular value or NULL
View as plain text  
My query isn't slow enough to tell the difference, I'm only working with a few 
hundred records.

This was intellectual curiosity.

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


>-----Original Message-----
>Sent: Wednesday, March 10, 2010 2:10 PM
>To: mysql@stripped
>Subject: Re: Particular value or NULL
>
>Did you gave a try on using coalesce in your query?
>
>""Jerry Schwartz"" <jschwartz@stripped> escreveu na mensagem
>news:02a201cac083$e07a2330$a16e6990$@com...
>> >-----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
>>
>>
>>
>>
>
>
>
>--
>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