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----- >From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] >Sent: Wednesday, March 10, 2010 2:10 PM >To: mysql@lists.mysql.com >Subject: Re: Particular value or NULL > >Did you gave a try on using coalesce in your query? > >""Jerry Schwartz"" <jschwa...@the-infoshop.com> escreveu na mensagem >news:02a201cac083$e07a2330$a16e69...@com... >> >-----Original Message----- >>>From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] >>>Sent: Wednesday, March 10, 2010 10:55 AM >>>To: mysql@lists.mysql.com >>>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"" <jschwa...@the-infoshop.com> escreveu na mensagem >>>news:023301cac069$366afa00$a340ee...@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=jschwa...@the- >>>infoshop.com >> >> >> >> > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwa...@the- >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
