Re: Execution plan: wrong index selected, very poor performance.

[Rick James]

SELECT  c2 c2, SUM(c10) c10
    FROM  t1
    WHERE  c1 >= DATE_SUB('2012-06-01 00:00:00',
                INTERVAL 0 SECOND)
      AND  c1 < DATE_SUB('2012-07-01 00:00:00',
                INTERVAL 0 SECOND)
    GROUP BY  c2
    ORDER BY  c2;

What percent of the table is that month? This can make a difference both in whether INDEX(c1) is chosen, and whether it is a 'good' choice. If most of the rows are in that month, the the index actually hurts performance -- it has to bounce between the index and the data.

Did you try the 'compound' index
INDEX(c1, c2, c10)
Then the entire query could be performed in the index.

Note the "767", then...
* Don't use VARCHAR(255) if the strings will always be short.
* Don't use utf8 if you know all the data will be ascii.
* "102" implies that 255 is serious overkill.

Always have an explicit PRIMARY KEY for InnoDB tables, even if it is an artificial INT UNSIGNED NOT NULL.

How much RAM do you have? Assign 70% of _available_ RAM to innodb_buffer_pool_size.

ALTER TABLE foo ORDER BY c1, c2, c10;

Are the values of those INTs (4 bytes each) small enough to fit into MEDIUMINT/SMALLINT/TINYINT (3/2/1 bytes). Smaller --> more cacheable --> faster.

> Why's it using key_c2 instead of key_c1?
* Using c1 avoids looking at all the data.
* Using c2 avoids a sort (one sort for both the GROUP BY and ORDER BY).
The optimizer has no practical way to decide which is better. Hence, it sometimes picks the wrong one.