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