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?
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Jogo
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
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
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
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é?
Jogo - <20070616111727.b395c713.jogo@matabio.net> :
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é?
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 Sat, 16 Jun 2007 09:30:46 +0200, "Mihamina (R12y) Rakotomandimby" a é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
Le Sat, 16 Jun 2007 09:30:46 +0200,
"Mihamina (R12y) Rakotomandimby" <infogerance@asso-polyvalente.fr> a
é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).
Le Sat, 16 Jun 2007 09:30:46 +0200, "Mihamina (R12y) Rakotomandimby" a é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
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.
Jérémy JUST - <20070616142847.5a6fbad0@norbert.jejust.info> :
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.
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 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.
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.
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
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 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 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
nobody - <46741528$0$19991$ :
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).
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).
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
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 ***********************
Mihamina (R12y) Rakotomandimby a écrit :
Jogo - <20070616111727.b395c713.jogo@matabio.net> :
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 ***********************
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 ***********************
