Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Aug 26 '11 at 15:12

This question came from our site for professional system and network administrators.

2 Answers 2

I noticed something interesting about your query: you are getting these totals for this year. The trendCut date is not a static value. It is a very bad candidate for an index column since the column value changes and it is neither first nor last in the column order of the index.

The index you need is this

monitorID,trendCut,nGram,nGramWord,total

However, this would still be deficient.

Suggestion: If you do queries that require trendCut by year, you should have a trendCutYear column. Try the following:

  • Create New wordtrend Table
  • Create TrendCutYear Column in the New wordtrend Table
  • Create New Index in the New wordtrend Table
  • Load the New wordtrend Table from the Old wordtrend Table
  • Keep the Old wordtrend table under another name

Here are those steps:

CREATE TABLE wordtrendnew LIKE wordtrend;
ALTER TABLE wordtrendnew ADD COLUMN trendCutYear SMALLINT NOT NULL;
ALTER TABLE wordtrendnew ADD INDEX (monitorID,trendCutYear,nGram,nGramWord,total);
ALTER TABLE wordtrendnew DISABLE KEYS;
INSERT INTO wordtrendnew
SELECT oid,monitorId,nGram,nGramWord,negatives,
neutrals,positives,total,trendCut,YEAR(trendCut)
FROM wordtrend;
ALTER TABLE wordtrendnew ENABLE KEYS;
ALTER TABLE wordtrend RENAME wordtrendold;
ALTER TABLE wordtrendnew RENAME wordtrend;

Now, you have a static trendCutYear. You can now query using a static value in the middle of the index. The query can now be rewritten using trendCutYear instead of trendCut:

SELECT nGram, nGramWord, SUM(total) AS sTotal,
SUM(positives), SUM(negatives), SUM(neutrals) FROM WordTrend
WHERE monitorId = 21751021 AND trendCutYear = 2011
GROUP BY nGram, nGramWord ORDER BY sTotal DESC;

This should perform a whole lot better for you.

Give it a Try !!!

share|improve this answer

According to MySQL Docs

The most important preconditions for using indexes for GROUP BY are that all GROUP BY columns reference attributes from the same index

Since your query includes nGramWord which is not in the proposed indexes, you're going to run into 'temporary' table.

Here is some more info on order by optimization, some specific instances where MySQL will use an index to generate the temporary table, but cannot help in ordering that apply to your case:

  • First, your order by column isn't in the index. But even if it was...
  • You have different ORDER BY and GROUP BY expressions.

I would probably create an index on (monitorID, trendCut) and resign yourself to creating the temporary table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.