Your query overloads expendituredate. Mysql now has to scqan the whole table to see if the new expenditure date will meet the where clause ( which it never will ) try DATE_FORMAT(expendituredate,"%F=%b-%Y) as expdate and see what happens.
kamesh jayachandran wrote: >Hi all, >I have a table named expenditure whose create statement is as follows, > > > expenditure | CREATE TABLE `expenditure` ( > `expenditureid` int(11) unsigned NOT NULL auto_increment, > `expendituredate` date NOT NULL default '0000-00-00', > `artifactid` smallint(5) unsigned NOT NULL default '0', > `amount` float default NULL, > `userid` int(11) NOT NULL default '0', > PRIMARY KEY (`expenditureid`), > KEY `searchbydate` (`expendituredate`), > KEY `searchbyartifact` (`artifactid`) >) TYPE=MyISAM > > I have 988 records in this table. > >My query is >select DATE_FORMAT(expendituredate,"%d-%b-%Y") as expendituredate,sum(amount) >as amount from expenditure where userid=11 and expendituredate between >'2002-8-01' and '2002-8-31' group by expendituredate order by >expendituredate; > >Total number of records that satisfy this date conditon is 283. >I have index on the expendituredate but it is not used >As my explain outputs the following > > +-------------+------+---------------+------+---------+------+------+-------- >---------------------+ >| table | type | possible_keys | key | key_len | ref | rows | Extra > | >+-------------+------+---------------+------+---------+------+------+--------- >--------------------+ >| expenditure | ALL | searchbydate | NULL | NULL | NULL | 988 | where >used; Using temporary | >+-------------+------+---------------+------+---------+------+------+--------- >--------------------+ >1 row in set (0.06 sec) > >I don't know why the query goes for the full table scan.(As the total no.of >rows that satisfy the where condition(date) is less that 30% of the total >number of records. > >Can anyone enlighten me in making the query use the index "searchbydate" >with regrads >kamesh jayachandran > >------------------------------------------------------------ > Get your FREE web-based e-mail and newsgroup access at: > http://MailAndNews.com > > Create a new mailbox, or access your existing IMAP4 or > POP3 mailbox from anywhere with just a web browser. >------------------------------------------------------------ > > >--------------------------------------------------------------------- >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 > > --------------------------------------------------------------------- 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
