MySQL Forums :: Optimizer & Parser :: Problems with query not using expeced indexes


Advanced Search

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 3405 Lars Olesen 03/09/2010 07:12AM
Re: Problems with query not using expeced indexes 1430 Lars Olesen 03/09/2010 07:13AM
Re: Problems with query not using expeced indexes 1457 Rick James 03/10/2010 10:28PM
Re: Problems with query not using expeced indexes 1416 Lars Olesen 03/11/2010 06:08AM
Re: Problems with query not using expeced indexes 1400 Rick James 03/11/2010 11:00PM
Re: Problems with query not using expeced indexes 1462 Lars Olesen 03/17/2010 12:16PM
Re: Problems with query not using expeced indexes 1533 Rick James 03/17/2010 09:57PM
Re: Problems with query not using expeced indexes 1256 Lars Olesen 03/18/2010 01:06AM
Re: Problems with query not using expeced indexes 1579 Rick James 03/18/2010 07:41AM


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.