Just saying thanks again! :)
Also just for reference, the reason why the query in the original post would not use the specified index is explained at
http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html#tight-index-scan
Assume that there is an index idx(c1,c2,c3) on table t1(c1,c2,c3,c4). The following queries do not work with the loose index scan access method described earlier, but still work with the tight index scan access method.
* There is a gap in the GROUP BY, but it is covered by the condition c2 = 'a':
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
*The GROUP BY does not begin with the first part of the key, but there is a condition that provides a constant for that part:
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
The query I was using was set up essentially like this:
INDEX: idx(c1,c2,c3)
SELECT c3 FROM table WHERE c1=18 AND c2 BETWEEN 'date_start' AND 'date_end' GROUP BY c3
The above query does not satisfy the conditions for either a 'Loose Index Scan' or 'Tight Index Scan' (the main issue is that c3 is NOT the leftmost part of the index. You can get around this with the 'Tight Index Scan' but one of the two conditions posted above must be met). Now, if the index was changed to this:
INDEX: idx(c3,c1,c2)
Then it would be used (c1 is a constant and satisfies the first tight index scan condition), but I'm not 100% sure if it covers only c3 and c1 or all of them.