Re: Problems with query not using expeced indexes

[Lars Olesen]

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