MySQL Forums
Forum List  »  Optimizer & Parser

Re: How to optimize group by and where
Posted by: Jørgen Løland
Date: November 07, 2011 07:06AM

Hi Eric,

When you do "GROUP BY id_magasin, id_produit", MySQL needs to get the rows in (id_magasin,id_produit) order. To do that, MySQL can read from a multiple keypart index where (id_magasin, id_produit) are the *first* keyparts. Otherwise, MySQL has to order the result before grouping.

The index you added does not have (id_magasin,id_produit) as the first keyparts, and the index can therefore not be used to ensure the ordering required to resolve GROUP BY. In fact, this query is only able to utilize the int_date keypart of the index. The condition "id_magasin IN (6, 2, 9, 5, 31)" will not be used in an index lookup. See why here.

You may get a speedup by adding an index on (id_magasin,id_produit), but that depends on the number of rows that meet each of the conditions:
"id_magasin in (6, 2, 9, 5, 31)"
and
"int_date BETWEEN 1148 AND 1178"

Please provide the output of SHOW CREATE TABLE and EXPLAIN (enclosed in [ code] [ /code] tags) if you have follow-up questions.

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
2031
November 06, 2011 01:36AM
Re: How to optimize group by and where
1026
November 07, 2011 07:06AM


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.