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. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
