In the last episode (Mar 01), Giuseppe Maxia said:
> Yesterday I was analyzing the behavior of the query optimizer, and I
> stumbled into a most curious case. I created two functions returning
> the extremes of a date range, and I wanted to see how many times
> those functions were called when used in a WHERE clause So I added
> log tracing instructions to both of them. The result was quite
> surprising. Let's set the environment first.
[..]
> I can't imagine why this is happening. The only difference is that dt
> is now primary key. Instead of being called once, the routine is
> called twice. If I simply drop the primary key in t2, then the
> routine is called once per query, as expected. The result does not
> change if I use InnoDB tables instead of MyISAM.
My guess is that the query optimizer is comparing the range endpoints
against the index to see whether it needs to do a full table scan,
index range scan, or optimize the table away. Depending on what data
is shared between the optimizer and the query enging itself, it may
need to evaluate the WHERE clause multiple times. Just a guess though;
examining the source, or replacing your stored procedure with a UDF
function that printed its own stack trace to a text file, would tell
you for certain.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
