List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:October 18 2005 6:17pm
Subject:Re: Fw: query help
View as plain text  
Dobromir Velev wrote:
> Hi,
> The following query will probably work but I think it will be easier to pass 
> the minimum date from your application.
> 
> SELECT * FROM t WHERE (year(dt)=year(Now()) and dt<Now()) or 
> (year(dt)=year(Now())-1 and month(dt)>month(Now()))
> 
> Also you might want to check the other Date and Time functions
> 
> http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
> 
> I'm sure there is a better solution then the above, and the query will be much 
> more optimized if you can send the minimum date from your application like 
> this.
> 
> SELECT * FROM t WHERE dt>$date;
> 
> Shawn's idea is also good - I just saw his response using the LAST_DAY() 
> function. 

The lack of optimization is not due to mysql, rather than the application, 
calculating the min date, it is because you are comparing a *function of a 
column* to a constant, instead of the value of a column to a (calculated) 
constant.  That is to be avoided if at all possible.

It's a simple as this:

   SELECT * FROM t WHERE col1 + 2 > 6;

cannot use the index on col1 to select rows, so it does a full-table scan, but

   SELECT * FROM t WHERE col1 > 6 - 2;

can use the index on col1 to select just the matching rows.

You should always make every effort to put the functions on the constant 
side of the comparison, not the column side.

Michael
Thread
Fw: query helpgrKumaran18 Oct
  • Re: Fw: query helpSGreen18 Oct
  • Re: Fw: query helpDobromir Velev18 Oct
    • Re: Fw: query helpMichael Stassen18 Oct
Re: Fw: query helpgrKumaran18 Oct
  • Re: Fw: query helpMichael Stassen18 Oct
Re: Fw: query helpMichael Stassen18 Oct