Skip to content

Instantly share code, notes, and snippets.

@lsolesen
Created March 10, 2010 14:00
Show Gist options
  • Save lsolesen/327883 to your computer and use it in GitHub Desktop.
Save lsolesen/327883 to your computer and use it in GitHub Desktop.
Make mysql query use an index
/* I want to run 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
/* On this 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 ;
/*
How can I make it use an index. Right now it is really slow. I added date_month just
to make the query use this index.
What I need is the SUM(price) for a customer on one month where the amount has
not already been processed. As soon as the amount is processed it will be set to 1.
How can I make the query use indexes?
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment