MySQL Forums
Forum List  »  Optimizer & Parser

Problems with query not using expeced indexes
Posted by: Lars Olesen
Date: March 09, 2010 07:12AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Problems with query not using expeced indexes
3449
March 09, 2010 07:12AM


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.