Indexing for group by + SUM query on mysql
I have a table with the following schema:
CREATE TABLE `wordtrend` (
`oid` bigint(20) NOT NULL AUTO_INCREMENT,
`monitorId` bigint(20) DEFAULT NULL,
`nGram` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`nGramWord` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`negatives` bigint(20) DEFAULT NULL,
开发者_运维问答 `neutrals` bigint(20) DEFAULT NULL,
`positives` bigint(20) DEFAULT NULL,
`total` bigint(20) DEFAULT NULL,
`trendCut` datetime DEFAULT NULL,
PRIMARY KEY (`oid`)
) ENGINE=MyISAM
AUTO_INCREMENT=358539
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Is it possible to create an index in order to run the following query efficiently?
SELECT nGram
, nGramWord
, SUM(total) AS sTotal
, SUM(positives)
, SUM(negatives)
, SUM(neutrals)
FROM WordTrend
WHERE monitorId = 21751021
AND trendCut >= '2011-01-01 00:00:00'
GROUP BY nGram
, nGramWord
ORDER BY sTotal DESC
We already tried the following:
KEY `RollupIndex` (`monitorId`,`trendCut`)
KEY `RollupIndex2` (`monitorId`,`nGram`,`trendCut`)
but we are getting "Using where; Using temporary; Using filesort" on the extra column. Thanks in advance.
- You sort by column without index so you get info Using temporary; Using filesort
- Using where; - you use where with or without index for searching in where clause.
You use ordering - 'ORDER BY sTotal DESC', so index on sTotal field might help.
Try different combinations of KEY (nGram, nGramWord, total).
Why not use EXPLAIN to get usefull info about the performance, and optimize your query ?!
加载中,请稍侯......
精彩评论