optimiser par INDEX

Le
Mihamina (R12y) Rakotomandimby
Bonjour,

J'ai une table de 600K lignes environs dont les colonnes sont (je simplifie
par rapport à la réalité, hein):

num_contrat, date_sinistre, montant_sinistre

On dénombre environ 1000 contrats dont les numéros sont de la forme
1234/345/456A (majoritairement des chiffres, mais avec une lettre et deux
ou trois slashes), des dates au format YYYY-MM-DD allant de 2004-XX-XX à
2007-XX-XX et les montants sont en FLOAT.

Telle quelle, aucune unicité envisageable par colonne, parceque nous avons
plusieurs sinistres par jour, des montants de sinistres qui peuvent etre
égaux et on peut avoir plusieurs sinistres par contrats.

La base: MySQL 4.1. et la prochaine sera sur une 5.0.

Supposons un "gros courtier" qui a 150 contrats sur les 1000, et on veut
extraire des sinistres sur ses 150 contrats.

On veut faire un

SELECT *
FROM ma_table
WHERE num_contrat='3454'
OR num_contrat='4564567/A'
OR num_contrat='098/123/E'
OR
OR
OR (y en a 150 comme ça);

Comme on peut s'y attendre, les perfs ne sont pas au rendez-vous.
Faut dire que la base est sur un Pentium4 HT aussi.
Le premier reflexe c'est d'y mettre un INDEX sur la colonne num_contrat.

Mais est-ce que je peux faire confiance à MySQL pour choisir le type d'index
qu'il faut? Pour ce type de requete et ce type de données, quel type
d'index devrait-on utiliser?

Merci d'avance.
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Jogo
Le #21852631
Sur fr.comp.applications.sgbd, Mihamina (R12y) Rakotomandimby disait :

Comme on peut s'y attendre, les perfs ne sont pas au rendez-vous.
Le premier reflexe c'est d'y mettre un INDEX sur la colonne
num_contrat.



Non. Le lien entre le courtier et le contrat ne devrait pas être
exterrieur à la base. Bref il te faudrait une table Contrats (liée à
une table Clients) et une table Courtiers.

--
Il n'est personne aujourd'hui, de vraiment cultivé,
pour parlé de la beauté d'un couché de soleil.
-- Wilde
Mihamina (R12y) Rakotomandimby
Le #21852621
Jogo -
Comme on peut s'y attendre, les perfs ne sont pas au rendez-vous.
Le premier reflexe c'est d'y mettre un INDEX sur la colonne
num_contrat.


Non. Le lien entre le courtier et le contrat ne devrait pas être
exterrieur à la base. Bref il te faudrait une table Contrats (liée à
une table Clients) et une table Courtiers.



...et donc faire une ou plusieurs jointures.
Mais justement, la table que j'ai est le resultat d'une jointure (créée à la
volée). On s'est dit que si on éliminait la jointure, on améliorerait les
perfs. En effet, 1000 contrat à lier avec 160K lignes, ça laisse quand même
penser que la jointure allait etre "lourde"... On s'est donc trompé?
Jérémy JUST
Le #21852611
Le Sat, 16 Jun 2007 09:30:46 +0200,
"Mihamina (R12y) Rakotomandimby" écrit :

des dates au format YYYY-MM-DD allant de 2004-XX-XX à 2007-XX-XX



Vous stockez les dates sous forme de texte? Pourquoi ne pas utiliser
un vrai type date, qui vous permettrait de faire de comparaisons?


Supposons un "gros courtier" qui a 150 contrats sur les 1000, et on
veut extraire des sinistres sur ses 150 contrats.



Je ne vois pas apparaître de table « courtier » dans ta description.
C'est un oubli?


SELECT *
FROM ma_table
WHERE num_contrat='3454'
OR num_contrat='4564567/A'
OR num_contrat='098/123/E'
OR ...
OR ...
OR (y en a 150 comme ça);



Déjà, une écriture avec IN(...) serait plus lisible:

WHERE num_contrat IN ('3454',
'4564567/A',
'098/123/E',
...
) ;


Le premier reflexe c'est d'y mettre un INDEX sur la colonne
num_contrat.



Vu le genre de requêtes que vous faites sur ce champ, il est évident
qu'il faut l'indexer.


Mais est-ce que je peux faire confiance à MySQL pour choisir le type
d'index qu'il faut?



Même si ce n'est pas le meilleur index qui soit, ce sera toujours
largement mieux que l'absence complète d'index.

Si vous voulez gagner un pouillème de plus, choisissez un champ de
longueur fixe (CHAR(16), par exemple), je pense que ça aidera pour la
tenue de l'index par rapport à un VARCHAR(16).


--
Jérémy JUST
Mihamina (R12y) Rakotomandimby
Le #21852601
Jérémy JUST -
des dates au format YYYY-MM-DD allant de 2004-XX-XX à 2007-XX-XX


Vous stockez les dates sous forme de texte? Pourquoi ne pas utiliser
un vrai type date, qui vous permettrait de faire de comparaisons?



Si, si. C'est _le_ type date.


Supposons un "gros courtier" qui a 150 contrats sur les 1000, et on
veut extraire des sinistres sur ses 150 contrats.


Je ne vois pas apparaître de table « courtier » dans ta description.
C'est un oubli?



Non. Il n'y a pas de table courtier.
On veut juste le total de sinistre pour un groupe d'une centaine de
contrats. J'ai mentionné "courtier" parcequ'un courtier s'occupe d'un
groupe de contrats.

SELECT *
FROM ma_table
WHERE num_contrat='3454'
OR num_contrat='4564567/A'
OR num_contrat='098/123/E'
OR ...
OR ...
OR (y en a 150 comme ça);



Déjà, une écriture avec IN(...) serait plus lisible:
WHERE num_contrat IN ('3454',
'4564567/A',
'098/123/E',
...
) ;




Effectivement.

Le premier reflexe c'est d'y mettre un INDEX sur la colonne
num_contrat.


Vu le genre de requêtes que vous faites sur ce champ, il est évident
qu'il faut l'indexer.



Merci.

Mais est-ce que je peux faire confiance à MySQL pour choisir le type
d'index qu'il faut?


Même si ce n'est pas le meilleur index qui soit, ce sera toujours
largement mieux que l'absence complète d'index.



Vu comme ça, oui.
Eric Rossé
Le #21852591
Le Sat, 16 Jun 2007 14:57:52 +0200, Mihamina (R12y) Rakotomandimby écrivait:

Non. Il n'y a pas de table courtier.
On veut juste le total de sinistre pour un groupe d'une centaine de
contrats. J'ai mentionné "courtier" parcequ'un courtier s'occupe d'un
groupe de contrats.



Il y a bien quelque part dans la table contrat un identifiant groupe
(ou courtier... peu importe comment il s'appelle) ?
Auquel cas, c'est lui qu'on indexe et c'est lui qui est utilisé dans
la requête.
nobody
Le #21852581

Comme on peut s'y attendre, les perfs ne sont pas au rendez-vous.
Faut dire que la base est sur un Pentium4 HT aussi.
Le premier reflexe c'est d'y mettre un INDEX sur la colonne num_contrat.




traduction en temps de reponse ? 5secondes? 1mn? 20mn? 2H ?
Mihamina (R12y) Rakotomandimby
Le #21852571
nobody -
Comme on peut s'y attendre, les perfs ne sont pas au rendez-vous.
Faut dire que la base est sur un Pentium4 HT aussi.
Le premier reflexe c'est d'y mettre un INDEX sur la colonne num_contrat.


traduction en temps de reponse ? 5secondes? 1mn? 20mn? 2H ?



Comme je l'ai dit, j'ai simplifié la présentation de mon souci.
La table réelle n'est pas aussi simpliste que celle que j'ai présentée.
Mais je viens de faire un essai avec ou sans INDEX, j'ai 20 minutes (sans
index) contre 30 secondes (avec index). Index par defaut (je n'ai ni
specifié B-TREE ni autre, j'ai laissé MySQL choisir).
Fred Brouard - SQLpro
Le #21852551
Mihamina (R12y) Rakotomandimby a écrit :
Jogo -
Comme on peut s'y attendre, les perfs ne sont pas au rendez-vous.
Le premier reflexe c'est d'y mettre un INDEX sur la colonne
num_contrat.


Non. Le lien entre le courtier et le contrat ne devrait pas être
exterrieur à la base. Bref il te faudrait une table Contrats (liée à
une table Clients) et une table Courtiers.



...et donc faire une ou plusieurs jointures.
Mais justement, la table que j'ai est le resultat d'une jointure (créée à la
volée). On s'est dit que si on éliminait la jointure, on améliorerait les
perfs. En effet, 1000 contrat à lier avec 160K lignes, ça laisse quand même
penser que la jointure allait etre "lourde"... On s'est donc trompé?



oui

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Publicité
Poster une réponse
Anonyme