List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:March 1 2006 3:27pm
Subject:Re: puzzled by date functions (long)
View as plain text  
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
puzzled by date functions (long)Giuseppe Maxia1 Mar
  • Re: puzzled by date functions (long)Dan Nelson1 Mar