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 ?!
精彩评论