LEFT JOIN avec forte cardinalité => tps infini

26 avr. 2005, 15:48

Bon, je vous rassure tout de suite, c'est qu'un demi probleme, j'ai déjà la solution, mais c'est juste que le temps d'exécution me semble ABERRANT donc je voudrais votre avis!

Mon probleme : faire des stats sur les les clients et les factures émises par mon entreprise durant un an.

Mon probleme :
Phase 1 : j'ai volontairement ignoré les clients dont la facture n'était pas encore saisie (INNER JOIN) => tps d'éxécution de la requête 59s

Phase 2 : j'inclue les clients n'ayant pas de ligne de facturation (LEFT JOIN) => tps d'exécution : inconnu (j'ai killé le process après 45min non stop sur mon P4 3Ghz + 512Mo configuré en mysql large)

et je me casse la tête dessus depuis j'ai tenté de changer de version de mysql : 4.1.11 et 5.0.4-beta => RIEN

=> j'ai essayer de lister / compter les numéros clients n'ayant pas de ligne de facturation même probleme

Code : Tout sélectionner

SELECT DISTINCT(`s`.`Dossier`)
FROM `séjours2004` s LEFT JOIN `ventes2004` v
ON `v`.`Dossier`=`s`.`Dossier`
WHERE `v`.`Dossier` is null

Mes cardinalités :
clients : 31 073 (pour 3Mo en base + 1.8Mo d'index)
factures : 918 134 (pour 28Mo + 39.3 d'index (j'en ai mis partout pour voir))

26 avr. 2005, 15:57

nouvelle tentative, j'ai simplifié les tables au possible mais avec les mêmes cardinalités :

Code : Tout sélectionner

CREATE TABLE `cli` (
  `dossier` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`dossier`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `fac` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `dossier` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`,`dossier`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

requête 1 :

Code : Tout sélectionner

SELECT count(distinct(c.`dossier`))
FROM `cli` c INNER JOIN `fac` f on f.`dossier`=c.`dossier`

=> 1.5sec

requête 2 :

Code : Tout sélectionner

SELECT distinct(c.`dossier`)
FROM `cli` c LEFT JOIN `fac` f on f.`dossier`=c.`dossier`
WHERE f.`dossier` is NULL

=> 500sec (et ca continue pendant que je post)

mais bon, j'ai découvers que j'ai ~28000 clients dans la table de facturation sur 31 000 référencés

26 avr. 2005, 16:18

bon, ben cherchez si vous voulez, mais je suis deg :
La base originale a été faite par mon boss qui n'y connais rien en SQL => avec MS access
taille de la base du boss 590Mo avec des champ DOUBLE(53,0) pour stocker un booléen, bref une horreur

Taille de la base de votre serviteur sous mysql (avec quelques optimisations) 80Mo

le bureau de mon boss étant verrouillé j'ai pas pu (pas voulu) tester la requête sous la vraie base jusque là. Mon boss viens de revenir, je teste la même requête que plus haut sur les 590Mo du mamouth => 11sec pour me sortir les 2100 clients concernés

JE NE COMPREND PAS

26 avr. 2005, 17:41

J'ai une appli comparable sur des cardinalités proches des tiennes. Ca ne devrait pas dépasser la seconde.

Je suppose également que tu n'as pas demandé l'affichage des résultats car 28.000 lignes à afficher, même en ligne de commande, ça prend du temps.

Pour tester la rapidité d'une requête, je remplace toujours le groupe SELECT par count(*). Il n'y a qu'une seule ligne à afficher.

26 avr. 2005, 21:15

Quelques suggestions (dans l'ordre) :
1. vérifier les types des données : DOUBLE(53,0), c'est peut-être beaucoup pour un simple booléen.
2. vérifier les duplications de données : si une table contient 10 000 fois la même valeur...
3. vérifier les redondances : si plusieurs tables contiennent les mêmes données...
4. corriger le modèle de données : j'ai l'impression que ce ne sera pas du luxe !
5. optimiser les requêtes : remplacer les IN par des JOIN, etc.

Je pense qu'avec ces 5 pistes, tu pourras résoudre beaucoup de tes problèmes de performances.

Sinon, je t'en suggère une dernière :
6. faire vérifier par ton boss à la main les résultats des requêtes en l'état.
Ça lui apprendra !

26 avr. 2005, 21:25

Sinon, je t'en suggère une dernière :
6. faire vérifier par ton boss à la main les résultats des requêtes en l'état.
Ça lui apprendra !

Non mais quel sadique ce type : prie pour que je t'embauche jamais, tu serais surveillé comme le lait sur le feu toi

Mais tu risques rien, j'ai même pas les moyens de m'embaucher moi-même

26 avr. 2005, 21:33

La base originale a été faite par mon boss qui n'y connais rien en SQL => avec MS access

Les coupables doivent être punis.
Il n'y a rien de sadique là-dedans. Ce n'est que Justice.

Et puis d'abord, appelle-moi "Monsieur le Président-Directeur Général" !

26 avr. 2005, 22:00

Oui not'bon maître

26 avr. 2005, 22:14

Rhâââ Lovely !

27 avr. 2005, 10:06

J'ai une appli comparable sur des cardinalités proches des tiennes. Ca ne devrait pas dépasser la seconde.

Tu l'as testé (si tu peux

?)

Je suppose également que tu n'as pas demandé l'affichage des résultats car 28.000 lignes à afficher, même en ligne de commande, ça prend du temps.

lol non, je te rassure, g commencé avec des count(*) mais là aussi ca me retournais rien. De toute facon, quelque soit la requête, je récupère un max de 3000 enregistrements (je groupe sur les codes produits

)

pour Albat :
Non mais tu me prend pour qui

! lol j'ai dit que j'était passé de 120Mo à 40Mo
1. Fait
2. Pas la peine : c des tables de prod => relativement propres (enfin, la base access a été crée depuis la base AS400 de prod à laquelle je n'ai pas acces)
3. Fait
4. Fait
5. Fait
6. Quels résultats ? la requête n'aboutit pas !

27 avr. 2005, 10:18

Notez bien que je ne suis certes pas le king of the SQL, mais une idée m'a traversé l'esprit: dans les cours de SQL que j'ai eu l'an dernier, on m'a signalé qu'on pouvait notablement accélérer les traitements en choisissant soigneusement les INDEX : peut-être indexer certaines colonnes des tables aiderait à réduire les délais ?

Tu l'as peut-être déjà fait remarque, mais comme ça n'était pas mentionné, je me suis dit que ça pouvait aider si peu que ce soit.

27 avr. 2005, 10:41

oui en fait, je l'ai tenté, sur la vraie base et sur une base "light" où seule les colonnes nécéssaires à la jointure étaient présentes...
Je pense sincèrement que c un bug de mysql sur ma machine avec ma base sur mon ordi
je vais essayer de piquer un autre ordi pour faire le test

27 avr. 2005, 12:00

Je pense sincèrement que c un bug de mysql sur ma machine avec ma base sur mon ordi

Je le pense aussi. Je penche plutôt pour des index corrompus.

Je suppose que tu as déjà essayé de les reconstruire? Optimize table peut-être?

Je t'ai fait un test sur mes bases:

1- Ici je force un NULL sur la table jointe (clients.codcli est toujours différent de ventes.codart)

Code : Tout sélectionner

SELECT count(*)
FROM clients
LEFT JOIN ventes ON clients.codcli = ventes.codart
WHERE ventes.codcli IS NULL

table ventes => 853038 (index sur codcli et codart)
table clients => 25120

count(*) => 25120 en 0.28 sec la première requête et 0.00 sec (!) dès la deuxième (normal, l'optimiseur MySQL a placé les index appropriés en mémoire)

2- clients.codcli non présents dans ventes

Code : Tout sélectionner

SELECT count(*)
FROM clients
LEFT JOIN ventes ON clients.codcli = ventes.codcli
WHERE ventes.codcli IS NULL

count(*) => 5490 en 0.02 sec la première requête et 0.00 sec (!) dès la deuxième .

Pendant le déjeûner, je lance un essai sans les index mais ça risque d'être (très très) long.

27 avr. 2005, 12:56

Comme je le pressentais, sans les index c'est une catastrophe. Au bout de 50 minutes, j'ai du tuer le PID mysqld!

Vérifie tes index.
As-tu essayé:

Code : Tout sélectionner

EXPLAIN SELECT .....
JOIN ... ON
WHERE ....

Le tableau retourné te montre si MySQL utilise effectivement tes index pour ta requête. S'il ne les utilise pas tu peux toujours essayer de le forcer avec un USE INDEX (ton_index) ou FORCE INDEX (ton_index) dans ta requête.

Tant qu'à faire (quoi qu'il y ai peu de chance que le problème se situe au niveau des types de colonnes) essaye aussi:

Code : Tout sélectionner

SELECT *
FROM ...
LEFT JOIN ... ON ...
WHERE ...
PROCEDURE ANALYSE()

Si le tableau retourné est vide, c'est tout bon. Sinon, change le type de colonne dans le type suggéré par MySQL

Après ça, je ne vois plus trop que faire...

27 avr. 2005, 14:49

Merci pour l'idée ripat mais j'y avais pensé : check + repair + optimize tout est OK
(j'ai eut un doute comme je repartait de données importées depuis accès mais ca a l'air bon)

Un pote me prete temporairement son serveur : http://jrdasm.no-ip.com/temp/cerber.rar (~2Mo) ce sont les fichiers d'une base myisam crée avec mysql 4.1.11 (l'actuelle 4.x)
C'est une base de test crée avec des valeurs random (les vraies données sont très sensibles)
la table client comporte environ 46 000 enreg avec des numéros de dossier entre 0 et 100 000
la table fact contient environ 600 000 enreg avec des numéros de dossier <950 (c'est le résultat d'un premier peuplement que je n'ai pas eut le courage de refaire)
Donc j'ai énormément de clients sans lignes de facturation (~95%) alors que dans la vraie base c'est que 2100 clients sur 31 000

a tester avec la requête :

Code : Tout sélectionner

SELECT *
FROM `cli2` c left outer join `fac2` f
on `f`.`dossier`=`c`.`dossier`
WHERE  f.`dossier` is null

le résultat du explain :

Code : Tout sélectionner

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","c","index","","PRIMARY",4,"",46618,"Using index"
1,"SIMPLE","f","index","","PRIMARY",8,"",686119,"Using where; Using index; Not exists"

le procedure analyse() (que je ne connaissais pas) ne peut aboutir : il attend les résultats de la requête qui ne se termine pas.
J'ai demandé a mon petit camarade de tester la requête chez lui, ca a mis son processeur à 100% et il a eut du mal a reprendre la main => MEFIEZ VOUS

je vais tester avec une version plus ancienne de mysql en attendant

LEFT JOIN avec forte cardinalité => tps infini

Qui est en ligne