Optimisation index composite : ordre des colonnes impacte-t-il vraiment ?

[zed.g]

Question théorique mais cruciale : sur un index composite (A,B,C), est-ce que l'ordre des colonnes change vraiment les perfs en pratique ? Je parle hors cas évidents comme les recherches par préfixe.

[neo92]

Oui, massivement ! J'ai benchmarké sur PostgreSQL 13 : un index (date, user_id) vs (user_id, date). Pour des requêtes WHERE user_id=? AND date BETWEEN x AND y, le second est 40% plus rapide. La sélectivité compte.

[julien_du_62]

Attention, ça dépend du SGBD. Sur SQL Server, l'ordre est moins critique grâce aux index columnstore. Mais pour MySQL/InnoDB, @144 a raison : mettez la colonne la plus sélective en premier.

[zed.g]

Intéressant ! @144 As-tu testé avec des tris ORDER BY ? Par exemple si je fais ORDER BY date, user_id, est-ce que l'index (date, user_id) devient meilleur malgré la moindre sélectivité ?

[neo92]

@88 Exactement ! Pour les tris, l'ordre dans l'index doit matcher l'ORDER BY. J'ai mesuré des x5 en perf dans ce cas. C'est le principe des 'covering indexes'. Un compromis à trouver selon l'usage.

[kevin69]

N'oubliez pas la cardinalité ! Sur notre table de 500M de lignes, un index (categorie, prix) est inutile car categorie a 3 valeurs. Mieux vaut (prio, categorie) même si les requêtes filtrent d'abord par categorie.