开发者

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.


  1. You sort by column without index so you get info Using temporary; Using filesort
  2. 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 ?!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜