MySQL Forums
Forum List  »  Optimizer & Parser

Re: Problems with query not using expeced indexes
Posted by: Lars Olesen
Date: March 17, 2010 12:16PM

I revised the table to look like this:

CREATE TABLE IF NOT EXISTS `daily` (
`id` int(10) NOT NULL auto_increment,
`error` varchar(255) NOT NULL,
`customerid` varchar(10) NOT NULL default '',
`dateDay` date NOT NULL default '0000-00-00',
`price` int(10) NOT NULL default '0',
`ourcalculatedprice` int(10) NOT NULL default '0',
`processed` int(1) NOT NULL default '0',
`date_month` int(6) NOT NULL,
PRIMARY KEY (`id`),
KEY `customerid` (`customerid`,`dateDay`,`processed`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

And that fixed the problem with the other query. However, I have another query which will not use the index. I am missing a basic point. Can you tell me what it is :)

SELECT customerid, customerid AS customer_id, SUM( price ) AS amount, DATE_FORMAT( dateDay, '%Y' ) AS year, DATE_FORMAT( dateDay, '%m' ) AS
MONTH
FROM daily
WHERE processed =0
AND dateDay <= '2010-02-28'
GROUP BY DATE_FORMAT( dateDay, '%Y%m' ) , customerid
ORDER BY dateDay ASC

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Problems with query not using expeced indexes
1680
March 17, 2010 12:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.