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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
