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 <mysql-thread118032@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
