List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:August 27 2002 1:25pm
Subject:Re: help in making the query to use the index
View as plain text  
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
>
>


Thread
help in making the query to use the indexkamesh jayachandran27 Aug
  • Re: help in making the query to use the indexGerald Clark27 Aug
RE: help in making the query to use the indexkamesh jayachandran27 Aug
  • Re: help in making the query to use the indexGerald Clark28 Aug