Comment MySQL décide-t-il d'utiliser un index pour GROUP BY ?

Question

J'ai un tableau simple

stock_ledger_id   INT(10) (Primary)
piece_to_bin_id   INT(10)
quantity          INT(11)
create_datetime   TIMESTAMP
... and a few VARCHARs

avec quelques index simples

Key_name          Cardinality
PRIMARY               1510443
piece_to_bin_id        100696

Cette requête plutôt simple prend environ 8 secondes :

SELECT piece_to_bin_id,
       SUM(quantity),
       MAX(create_datetime)
FROM stock_ledger
GROUP BY piece_to_bin_id

Voici l'EXPLIQUE :

id select_type table        type possible_keys key  key_len ref  rows    Extra                           
1  SIMPLE      stock_ledger ALL  NULL          NULL NULL    NULL 1512976 Using temporary; Using filesort 

J'ai trouvé que Je peux le réduire à environ 0,5 seconde. en forçant un index :

SELECT piece_to_bin_id,
       SUM(quantity),
       MAX(create_datetime)
FROM stock_ledger
FORCE INDEX (piece_to_bin_id)
GROUP BY piece_to_bin_id

Alors l'EXPLAIN ressemble à ceci :

id select_type table        type  possible_keys key             key_len ref  rows    Extra
1  SIMPLE      stock_ledger index NULL          piece_to_bin_id 4       NULL 1512976

J'utilise MySQL 5.1.41, la table est MyISAM et j'ai exécuté ANALYZE TABLE auparavant.

Je suis donc coincé avec "MySQL s'est encore trompé, forcez l'index" ou y a-t-il une raison réelle pour laquelle MySQL utilise un balayage complet de la table ? Peut-être une raison que je peux corriger ?

Answer 1

La requête nécessite de toute façon un balayage complet de la table, il se peut que mysql essaie d'éviter la transition supplémentaire entre la valeur de la clé et la ligne. La requête pourrait bénéficier bien plus d'un index composite (piece_to_ bin_id, create_datetime) ou même (piece_to_ bin_id, create_datetime, quantity). Ce dernier deviendrait un index de couverture.

UPD

Il semble que le résultat 16x plus rapide provienne de la distribution des données dans votre cas (probablement, beaucoup de lignes adjacentes avec la même piece_to_bin_id trié par create_datetime ). MyISAM semble utiliser les index pour les requêtes qui réduisent le nombre de lignes résultantes, car leur utilisation implique des opérations d'E/S disque aléatoires.

Je n'ai jamais attiré l'attention sur ce point, mais mes tests actuels sur une table de 10 000 lignes montrent que MyISAM n'utilise même pas l'index pour trier une requête de ce type :

SELECT indexed_field, another_field
FROM a_table
ORDER BY indexed_field;

Même lorsque le indexed_field est la clé primaire.