List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 2 2001 3:11am
Subject:Re: Query speed
View as plain text  
At 10:26 AM +0000 11/1/01, Leon Noble wrote:
>Hi All,
>
>Tried the following three statements and they are wither too slow or do not
>give me what I want. Basically what I want is to search for records for a
>whole month and display totals for that month for each individual day. The
>date field is indexed.

It might be indexed, but all your tests on the field involve values
that must be calculated from the date, which makes the index useless.
(Every row must be fetched so that the expressions can be evaluated
and tested.)

Since you apparently want dates in the month of August, 2001, try
using a WHERE clause of:

WHERE date >= '2001-08-01' AND date <= '2001-08-31'

or

WHERE date BETWEEN '2001-08-01' AND '2001-08-31'

Either of these involve no transformation on the date values and the index
can be used.  That may speed up your query.

>
>Tried......
>
>select count(num) as mycount from table_name where month(date) = 08 and
>year(date) = 2001 and action = 1 group by dayofmonth(date);
>
>this one takes too long........
>
>
>As does this one.....
>
>select dayofmonth(date) as mydate, count(num) as mycount from table_name
>where month(date)=08 and year(date)=2001 and action=1 group by
>dayofmonth(date);
>
>
>and this one just outputs the total figure for the month
>
>select dayofmonth(date) as mydate, count(num) as mycount from table_name
>where date='TO_DAYS(2001-08-01) - TO_DAYS(2001-08-31)' and action=1 group by
>dayofmonth(date);
>
>
>can anyone help to point me in the right direction.
>
>many thanks
>
>Leon.
Thread
Query speedLeon Noble1 Nov
  • Re: Query speedCarl Troein1 Nov
  • Re: Query speedPaul DuBois2 Nov
RE: Query speedQuentin Bennett1 Nov