Efficiency of NULLIFie expression

ADDDATE(ereStart, (SELECT hwyl FROM Stock) * NULLIF(Houmuch, (SELECT SharePrice FROM Stock)) / (SELECT regularPayment FROM Stock)) AS goodThrough

Buried in the middle of the foregoing expression is a NULLIF, because of which the whole thing is NULL if "houmuch" and "SharePrice" are equal. Thus to write it is quite convenient--but if the optimizer is not clever enough, there will be wasted effort in propagating the NULL.

Is the optimizer so clever as to set that test outside the expression, and only then evaluate it when "houmuch" and "SharePrice" are not equal?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Efficiency of NULLIFie expression

hsv@tbbs.net wrote:
(...)
>
> Is the optimizer so clever as to set that test outside the expression, and only then evaluate it when "houmuch" and "SharePrice" are not equal?
>
>

Try it yourself:
mysql > explain extended select * from table1;

and then
mysql > show warnings;

You will see the optimized query.

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Regards,
m

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Efficiency of NULLIFie expression

>>>> 2011/05/12 15:15 +0200, misiaq >>>>
hsv@tbbs.net wrote:
(...)
>
> Is the optimizer so clever as to set that test outside the expression, and only then evaluate it when "houmuch" and "SharePrice" are not equal?
>
>

Try it yourself:
mysql > explain extended select * from table1;

and then
mysql > show warnings;

You will see the optimized query.

http://dev.mysql.com/doc/refman/5.0/en/explain.html
<<<<<<<<
Not much of an explanation of "EXPLAIN"--and this command is mostly about indexing and joining, in SQL of course of immens importance, and, to be sure, finding "houmuch" in my query costs JOINing and SUMming, but I see nothing relevant to my question. On the other hand, maybe finding "houmuch" costs so much more than the rest of the expression that it does not matter.

In any case, there is here no answer to my question.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Efficiency of NULLIFie expression

hsv@tbbs.net wrote:
> >>>> 2011/05/12 15:15 +0200, misiaq >>>>
> hsv@tbbs.net wrote:
> (...)
> >
> > Is the optimizer so clever as to set that test outside the expression, and only then evaluate it when "houmuch" and "SharePrice" are not equal?
> >
> >
>
> Try it yourself:
> mysql > explain extended select * from table1;
>
> and then
> mysql > show warnings;
>
> You will see the optimized query.
>
> http://dev.mysql.com/doc/refman/5.0/en/explain.html
> <<<<<<<<
> Not much of an explanation of "EXPLAIN"--and this command is mostly about indexing and joining, in SQL of course of immens importance, and, to be sure, finding "houmuch" in my query costs JOINing and SUMming, but I see nothing relevant to my question. On the other hand, maybe finding "houmuch" costs so much more than the rest of the expression that it does not matter.
>
> In any case, there is here no answer to my question.
>
>

You're right about explain. That is why I suggested to use explain EXTENDED and then SHOW WARNINGS.
Second query will show you the optimizer output. As far as I understand - that was the par of your question.

Regards,
m

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Efficiency of NULLIFie expression

>>>> 2011/05/14 21:47 +0200, misiaq >>>>
You're right about explain. That is why I suggested to use explain EXTENDED and then SHOW WARNINGS.
Second query will show you the optimizer output. As far as I understand - that was the par of your question.
<<<<<<<<
Yes, I did that.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Efficiency of NULLIFie expression

>>>> 2011/05/14 21:47 +0200, misiaq >>>>
You're right about explain. That is why I suggested to use explain EXTENDED and then SHOW WARNINGS.
Second query will show you the optimizer output. As far as I understand - that was the par of your question.
<<<<<<<<
Yes, I did that. That output showed nothing at all interesting about the NULLIF that interests me. It was only an expansion like those found in the dumps, only fuller.

Too bad.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org