Problems with query not using expeced indexes
The following table:
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`,`date_month`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Should be running this query:
SELECT SUM( price ) AS total_price, date_month
FROM daily
WHERE processed =0
AND date_month = '200910'
AND customerid =1
GROUP BY date_month, customerid
But I cannot get it to use indexes. The date_month has only been added to try have a row which could be used in the index.
So I need the SUM(price) of a customer in one month. There are 1.700.000 rows so an index has to be used somehow. When the rows has been calculated and invoiced processed will be set to 1.
How can I make the query use indexes?