List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:July 18 2005 3:10pm
Subject:Re: creating a faster query
View as plain text  
Brent Baisley wrote:

> Don't look at it as a string, you're not searching on a string. What  
> you are actually searching on is a range of dates, the first of the  
> month through the end of the month. That will keep your data in a date 
> format and use the index.
> 
> SELECT ... WHERE date between CONCAT(YEAR(date),"-",MONTH(date),"-1")  
> AND LAST_DAY(date)

That will match all rows (every date is in its own month).  Perhaps you meant 
something like

   SET @targetdate = '2005-06-15';

   SELECT ...
   WHERE date BETWEEN CONCAT(YEAR(@targetdate),"-",MONTH(@targetdate),"-01")
     AND LAST_DAY(@targetdate);

I'd also suggest that your CONCAT(YEAR...MONTH...) is duplicating 
functionality already provided by DATE_FORMAT():

   SET @targetdate = '2005-06-15';

   SELECT ...
   WHERE date BETWEEN DATE_FORMAT(@targetdate,'%Y-%m-01')
     AND LAST_DAY(@targetdate);

Of course, if you're doing this in an application, skip the SET statement and 
just put the target date variable in place of @targetdate in the SELECT.

Michael
Thread
creating a faster queryOctavian Rasnita16 Jul
  • Re: creating a faster queryMichael Stassen16 Jul
  • Re: creating a faster queryBrent Baisley18 Jul
    • Re: creating a faster queryMichael Stassen18 Jul