Bonjour,

Je suis face à une question où l'on me demande les 4 cas où il est pertinent de créer un index sur une colonne.

Parmi ceux que je pense il y a :
- Augmenter la rapidité des SELECT lors de jointures.
- Imposer des contraintes sur les clés étrangères.
- ?
- ?

Je ne suis pas sur si mes 2 premières propositions sont tout à fait justes, et les deux autres je ne sais pas, pourriez-vous m'aider s'il vous plait ?


PS: Y aurait-t-il plus de 4 raisons, quelles seraient ces raisons ?


Je vous remercie.

- Augmenter la rapidité des recherches sur les colonnes indexées (SELECT,UPDATE,DELETE,jointures,etc)
- Poser une contrainte d'unicité
- Éviter les tris (ORDER BY + LIMIT)
- Éviter les accès à la table (certaines bdd peuvent utiliser uniquement l'index)
- etc

Imposer des contraintes sur les clés étrangères. => l'index n'est nécessaire que sur certaines bdd

Merci pour ta réponse Lord Casque Noir, mais j'aurai juste besoin de quelques précision, tu peux répondre par oui ou par non


1. Donc si je comprends bien, les colonnes indexées augmente la rapidité des recherches des clauses WHERE (pour les SELECT, UPDATE et DELETE) et des conditions de jointures (pour les SELECT) ?


2. Pour éviter les tris... je ne comprends pas, est-ce que cela signifie qu'on n'a plus besoin de préciser ORDER BY lorsqu'on fait un SELECT si nous avons une clé primaire en auto-incrément ?


3. Lorsque tu dis éviter les accès à la table, est-ce qu'il y a des tables dont nous sommes obligé de préciser un index dans la clause WHERE pour avoir accès (à la table) ?

> Pour éviter les tris

Quand tu fais "SELECT * FROM joueurs ORDER BY score DESC LIMIT 5" et que tu as un index sur score, la BDD va utiliser l'index pour prendre les 5 meilleurs scores plutôt que de trier toute la table. Un index de type btree est déjà trié. Bien sûr il faut toujours mettre le ORDER BY, mais la BDD n'est pas obligée d'effectuer réellement le tri si il y a moyen de l'éviter.

> éviter les accès à la table

Certaines BDD peuvent utiliser uniquement l'index sans devoir regarder la table. Par exemple, si tu fais "SELECT score FROM joueurs ORDER BY score DESC LIMIT 5" et que tu as un index sur score, la BDD peut prendre les données directement dans l'index. Par contre si tu fais "SELECT * FROM joueurs ORDER BY score DESC LIMIT 5" il faudra regarder dans la table pour prendre les autres colonnes.

> nous sommes obligé de préciser un index dans la clause WHERE

non c'est purement une optimisation

Bonjour Lord Casque Noir,

J'ai trouvé un tutoriel très intéressant sur les index sur ce site, néanmoins il y a quelques contradictions avec ce que tu m'as dis.

En particulier ce point :

Citation : Extrait du tutoriel

Intérêt des index
tout l'intérêt des index est d'accélérer les requêtes de sélections qui utilisent comme critères de recherches des colonnes indexées.


Désavantages

Ils ralentissent les requêtes d'insertion, modification et suppression, puisqu'à chaque fois, il faut remettre l'index à jour en plus de la table.

Ai-je mal compris ton explication ?



Sinon j'aurai 3 autres petites questions liés aux index simples, serait-t-il pertinent de créer une table avec beaucoup d'index, en considérant que nous sommes susceptible d'effectuer une recherche sur chacun de ces colonnes :
ID (Clé Primaire) | Nom (Index Simple) | Prenom (Index Simple) | InitialeDeuxiemePrenom (Index Simple)

1. Avec une requête ne prenant en compte qu'une seule colonne dans le WHERE ?
2. Avec une requête prenant en compre Nom, Prenom et InitialeDeuxiemePrenom dans le WHERE ?
3. Comment le SGBD se débrouille avec 3 index ? Faut-t-il lui indiquer un ordre pour commencer par Nom, puis Prenom, puis InitialeDeuxiemePrenom ou il se débrouille tout seul ?




Je te remercie !

Si tu fais "UPDATE blabla WHERE machin=truc" et que tu as un index sur "machin", ça accélère la recherche de la ligne à modifier (il faut bien la trouver pour pouvoir la modifier). Donc un index accélère toutes les opérations de recherche (le select étant le cas le plus fréquent).

Mais effectivement à chaque écriture, il faut tenir l'index à jour, ce qui ralentit ces opérations.

Donc, ça dépend de la quantité d'écritures que tu fais.

Pour ton autre question, un index est efficace quand il est sélectif, par exemple "WHERE machin=truc" où la colonne "machin" a la valeur "truc" dans 1% des lignes. Si la valeur recherchée est présente dans 90% des lignes, ça ne sert à rien d'utiliser l'index, autant lire toute la table.

Donc pour ton exemple, si tu fais souvent des recherches sur les colonnes en question, ça vaut la peine. Sauf peut-être l'initiale.
Tu peux aussi mettre des index sur 2 colonnes, par exemple (nom,prenom)

Pour le 3, ça dépend des SGBD, certains sont capables de croiser plusieurs index, d'autres non.