MySQL Forums
Forum List  »  Optimizer & Parser

Re: GROUP BY Not Using Index
Posted by: Conner Hewitt
Date: February 08, 2011 11:03AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
8351
January 19, 2011 08:56AM
3962
January 20, 2011 10:02PM
2948
January 21, 2011 05:04PM
2556
January 21, 2011 11:07PM
Re: GROUP BY Not Using Index
2589
February 08, 2011 11:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.