List:General Discussion« Previous MessageNext Message »
From:João Cândido de Souza Neto Date:March 10 2010 7:09pm
Subject:Re: Particular value or NULL
View as plain text  
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
>
>
>
> 


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