J'ai lu quelques topics sur la répartition des index mais je doute toujours sur quelques points dans la création des index.
Bon à part les index initialement créés sur les PK, j'en ai aucun (précision je suis sous SQL Server 2000)
J'ai surtout une table de 2 000 0000 de lignes et 23 champs sur laquelle j'update les données peut être une fois par semaine ou mois (et toutes les données ne sont pas mises à jour). L'identifiant de chaque ligne est une sorte d'auto-incrément régulé par un trigger qui insère en priorité dans les trous laissés par les DELETE.
L'application php en relation avec, permet de faire une recherche dans cette table(et qq autres annexe mais petites) en fonction de nombreux critères comme nom, prénom, n° de tél, portable, ville, dates ... ; et une procédure SQL réalise aussi le m type de recherche pour un autre cas d'utilisation.
Combien d'index dois-je déclarer sur cette table pour optimiser la recherche ?

Ben... C'est simple : de base, tu imagines autant d'indexes qu'il y a de critères différents dans tes différentes requêtes.
 
Par exemple, tu as trois requêtes :
 
select *
from bidule
where nom like '%toto%'
 
select *
from bidule
where numtel = '0123456789'
 
select *
from bidule
where nom like '%toto%' and numtel = '0123456789'
 
=> Tu devrais faire trois index, un sur le nom uniquement, un sur le numéro de téléphone, et un troisième sur le nom et le numéro de téléphone.
 
Après, tu dois te poser deux questions :
- Lesquels sont rarement utilisés ? (tu les vire)
- Lesquels sont inclus "dans le débuts des autres" ? Ceux là, tu les vire.
 
Dans ton cas :
 
celui sur nom est inclu dès le début dans celui portant sur les deux champs.
Tu peux donc le virer.
 
Ensuite, tu déduis de l'utilisation qu'il est rare de faire une recherche que sur le numéro de téléphone. Tu peux donc le virer.
 
Ceci dit, si les données chances rarement, tu peux sans problème mettre beaucoup d'index.
 
A noter que si tu as des doublons dans les informations "nom, prénom, tel, etc.", alors ta base est mal faite. Fait une table "personne", dans laquelle tu met que ces infos, avec un ID, et tu ne met que cet ID dans ta grosse table.
 
Ainsi, ta petite table "personne" aura tous les index nécessaires pour retouver une personne facilement, et dans ta grosse table, tu n'auras qu'un unique index sur "id_personne".

Merci pour cette explication des index, c'est la première fois que je vois une démarche à suivre pour leur création.
Je viens d'être embauché dans une boite où ils me demandent, pour découvrir les tables, d'essayer d'optimiser les requêtes.
 
Maintenant j'aurais quelques questions:
 
- Quand considère t on qu'une table est souvent mise à jour ? est ce que c'est fonction de la fréquence de mise à jour ou de la quantité de données mise à jour ? (ex: j'ai une table qui est changée quotidiennement mais seulement de 10 lignes environ, suppressions, modifications et ajouts confondus)
- Dans le cas de tables modifiées régulièrement, comment optimiser les requêtes ? Moins d'index ? Autre méthode ?
- Comment utilise t on les index quand on travaille sur des vues ?
- J'ai vu qu'on ne mettait pas d'index sur les clés primaires, mais dans le cas d'une vue, la clé est répétée dans chaque table qui la compose, doit on mettre un index sur ces clés secondaires ? (clés primaires d'une table, retrouvées dans d'autres tables, et servant a faire le lien pour la vue)
 
J'aurais certainement d'autres questions qui me viendront plus tard, en y travaillant dessus, mais ça serait sympa de m'éclairer un peu
 
MERCI

pour moi, une table souvent modifiée, ça se traduit par l'un ou l'autre :
- le ratio entre lecture/écriture est inférieur à 2
- plus de 5% des lignes sont mises à jour par jour
- un grand nombre de DELETE est effectué dans la table régulièrement
- plus de 2 index sont systématiquement mis à jour lors des updates le plus réguliers de la table
 
en gros, tout de même, il faut prendre en compte le volume :
10 lignes bougées par jour, c'est faible.
10 par heure, c'est juste aussi
10 par minute, c'est déjà plus préoccupant
10 par seconde, là oui c'est une table qui bouge souvent
 
le souci d'une table souvent mise à jour, c'est qu'il faut réduire au maximum la taille et le nombre des index : en effet, sinon le temps de mise à jour des index sera plus long que le temps gagné grace aux index. mais pour observer ceci, il faut quand même avoir une table volumineuse, et réellement un grand nombre de lignes mises à jour, et de façon très étallée dans le temps : si t'as 100 000 lignes mises à jour toutes les nuit, de minuit à 1 heure, même si ça représente 50% des données de la table, on ne pourra pas dire qu'il y a beaucoup de mouvements dans la table : en effet, quelque soit le temps pris pour la modif et le temps nécessaire au SGBD pour s'en remettre, il n'impacte pas son utilisation (qui se fait normalement la journée )

sinon, on en reparle ce soir, là je suis au boulot et j'ai pas mal de trucs à faire, pas le temps d'écrire un gros patté
 
ps: je risque de sortir très tard du boulot, si tu passes à 22h dedans un PC, t'as qu'à faire remonter le topic, je devrais être sur le départ

ok merci bcp
 
Je vais en rajouter, puisque j'y suis !
 
Connais tu un moyen efficace et précis de tester la durée d'une requête SQL ? (pour vérifier si je gagne du temps en faisant mes changements ou pas...)
 
merci

ben... pour le moment, je ne peux toujours pas de répondre...
 
pffff, relou, depuis que j'ai changé de boulot, j'ai plus le temps de rien faire
 
 
ce soir, mon patron vient se pointer chez moi pour bosser (hier j'ai déjà fini le boulot à 0h30... )
 
pas gagné pour te donner des réponses ce soir.
 
en tout cas, n'oublie pas de faire remonter le topic en fin de soirée, que je le trouve facilement (là vu ue j'ai répondu, mon drapal n'est plusactif )

au fait, c'est quoi ton SGBD ? (ça ira mieu pour répondre à tes questions)

héhé, je comprends bien, ya pas de souci !
On travaille sur SQL Server.
Répond moi quand tu auras le temps, j'essaie de me débrouiller en attendant, je recherche, je fouille...
 
merci

 
 
2 ne serait pas suffisants?
 
-nom, numero
-numero
 
la requete 1 prendra l'index (nom, numero),  comme la 3.
 
Edit: Je viens de lire la suite

Y'aussi une méthode barbare qui marche bien pour ns ; toutes les nuits on a en moyenne 300 000 lignes à ajouter ds une table ; méthode utilisée :
. on supprime tous les index de la table
. on importe les données
. on recrée les index
 
Pour ns çà se révéle + rapide que d'importer les données avec les index existants.
 
Je précise : la table est monstrueuse ( 10 000 000 lignes / mois et doit y avoir une dizaine d'index )

A defaut de les supprimer, tu dois pouvoir les desactiver.

Même si on peut les désactiver, il y aura qd même une procédure de mise à jour des index, donc on ne gagnerait que la suppression qui elle est instantanée, non ?

c'est en effet une bonne solution.
 
seulement, elle a un gros point faible : si ça plante, boum, plus d'index pour la journée suivante, et sur une table de cette taille, c'est dangereux.
 
mais sinon, elle est en effet très courrante.
 
sinon, "ns" c'est quoi ? pour moi, c'est "nomadsoft", mais je ne pense pas que ce soit ça

lol
'ns' c'est pour 'nous'

 
PAs desactiver l'index pour les acces, mais pour les ecritures. Il me semble que c'est faisable (Sybase) de couper la maj de l'index pour les grosses insertions.

cinocks > pour un gros lot d'insert, je pense qu'il est intéressant de toute façon de faire un LOCK exclusif sur la table : les écritures et les maj des champs ne sont pas parasités par des lectures. sinon, la reconstruction complète (donc drop puis create) est intéressante tout de même dans ce cas, et surtout elle permet de correctement équilibrer et optimiser les index (car si on ne fait rien à l'index après un lot d'insert, le SGBD ne rééquilibre pas tout seul dans la foulée)

exact.
 

up !
 
- Connais tu un moyen efficace et précis de tester la durée d'une requête SQL ? (pour vérifier si je gagne du temps en faisant mes changements ou pas...)  
- Comment utilise t on les index quand on travaille sur des vues ?  
- J'ai vu qu'on ne mettait pas d'index sur les clés primaires, mais dans le cas d'une vue, la clé est répétée dans chaque table qui la compose, doit on mettre un index sur ces clés secondaires ? (clés primaires d'une table, retrouvées dans d'autres tables, et servant a faire le lien pour la vue)

Une vue n'est rien d'autre qu'une requete SQL. Il faut donc que les index soient bien placés sur les tables constituant la vue. Et ils doivent etre concus en fonction des criteres de selection fait sur la vue.
 
Il me semble qu'il y a forcement un index d'associé pour une clé primaire. Ce qui me parait logique, ne serait-ce que pour verifier, lors d'insertion/update, qu'il n'y aura pas de doublons.
 

cinocks > pas d'accord : dans une vue, le plan d'exécution est compilé. cela peut très facilement diviser le temps d'execution par 2 ou 3. Plus la requête est complexe, les index nombreux dans les tables, et plus on gagne.

sinon, oui, toute PK fait l'objet d'un index unique non null.

ceci dit, c'est le paramètre par défaut, c'est désactivable (et non implicite dans les modélisations UML et MERISE)

 
Qu'entends-tu par 'compilé' ?
 
Il me semble que ce n'est pas le cas avec Oracle, que le plan dexécution d'une requête utilisant une vue est calculé à l'exécution de la requête, sauf s'il est déjà en cache bien sûr.

 
je n'ai pas dit le contraire. Mais une vue reste une simple requete derriere, meme si elle est compilée. Il faut donc voir les index sur les tables comme si ce n'etait qu'une requete.

j'ai confondu deux topic, d'où ma réponse qui en effet ne concernait pas ta réponse

y'a pas de mal.

Salut a tous !
 
Comment faites vous de votre coté pour tester la pertinence d'un index ?
Moi j'en rajoute mais le temps d'exécution de la requete reste le meme (en tout cas en apparence)
 
ya t'il des outils pour tester précisément la durée d'une requete ?

faire attention au moteur utilisé. La plupart du temps le resultat de la requete est dans le cache si les données n'ont pas changées. Il ne faut donc qu'elles atterissent dans le cache.
 
En utilisant MySql, mets un SELECT SQL_NO_CACHE .....
 
Sinon pour la pertinence, il n'y a pas forcement besoin de tester. Il suffit de voir les requetes à faire et mettre en place les index en fonction.
 
Sinon un SHOWPLAN ou EXPLAIN (je ne sais plus lequel) devant la requete va donner son plan d'execution. Toujours MySql, le plan d'execution est expliqué dans la doc en ligne.

Merci cinocks !!
J'avais essayé de regarder le plan d'exécution, mais je ne vois que des couts dont on ne sait rien... couts d'entrée/sorties, couts CPU...
 
Tous ces coups ne m'interessent pas, et je ne trouve rien en terme de temps.
Le temps total de la requete, le bench.
 
J'ai vu que Arjuna en avait fait un dans ce topic http://forum.hardware.fr/hardwaref [...] 4576-1.htm
Par contre je ne connais pas ce langage mais c'est exactement ce que j'aimerais faire.
 
En lançant une premiere fois la requete sans les index et la 2e avec.
Voir s'il existe une différence... meme minime...
 
merci

Salut,
 
Dans mon topic, c'est du T-SQL (langage procédural de SQL Server) - équivalent du PL/SQL d'Oracle et PostGre).
 
Pas de chance, MySQL ne supporte pas encore ce type de langage. (sauf les toutes dernières version, et ça doit encore être à l'état de béta)

 
 
Le temps est lié au x couts. Plus ta requete sera couteuse plus elle sera longue . Les IO sont des infos tres importantes, tout comme le nombre de pages memoires chargées. Beaucoup d'IO rime tres souvent avec un index mal formé.

ok merci à vous !
 
J'essaierai de faire avec ces nouvelles informations !

Salut à tous.
 
Je viens de lire vos articles et le topic sur les colonnes calculées.
Je ne trouve pas d'infos concernant le "recalcul" des index.
Je m'explique, j'ai une requête sur Sage ligne 100 (faite par l'installateur) qui tape dans SQL Server. Environ 30 secondes par pages    
et une moyenne de 6 pages...  
Je pensais l'améliorer avec un index sur le champs de classement qui n'est pas une PK.
Rien...  Pas de changement !
Faut-il spécifier au SGBD de recalculer les index ?
Il me semblais que c'était automatique.
J'ai vu aussi une case à cocher "PAD INDEX"...késako ?    
 
Merci

j'ai pas vraiment compris la question. De quels pages parle tu? pages memoires? pages d'edition....
 
Sinon, lorsque tu créés un index, il se construit automatiquement. C'est le cas par la suite. Il se mets à jour à chaque ation sur la table. Par contre, lors de gros traitements de modification (insert, delete et update), il peut etre utile de mettre à jour l'index (forcé). Dans ce cas, il va le reorganiser un peu comme le ferait un un defragmenteur de fichiers.
 
Sinon, il faut voir si ta requetes prend bien le nouvel index. Et là c'est le plan d'execution qui te le diras.

Concernant les index, ta réponse me rassure, il me semblais bien que c'était automatique.
 
En fait je parle de pages de résultat qui s'affiche à l'écran.
La base doit faire 200 000 lignes et il n'y a pas "grosses modifications".
Le pb c'est que les informations sont vraiment mélangées. Il s'agit d'écritures comptables.
Donc recherche + calcul...waouh ..t'as le temps de prendre un café.  
 
Bon je vais voir du coté du plan d'exécution.
J'ai déjà fait ça sous Oracle mais pas sous SQL Server
Si tu as des infos, je suis preneur...

Je viens de télécharger la doc SQLServer
J'ai de la lecture ...
 
PS : Si ça intéresse quelqu'un :
http://www.microsoft.com/downloads [...] laylang=fr
 

bonne lecture.  
regarde du coté du plan d'execution, des IO, et des couts d'execution.