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
