optimisation de temps de reponse des requetes sql

[mv_murph]

bonjour,

je travaille sur une table qui a enormement d'enregistrement (+ de 5 millions)

Mon probleme est que le temps de reponse est super long (je bosse en local pour l'instant). Du coup qd je lance une requete (en general des count(id) ) ben il arrete au bout d'un moment avec un message d'erreur comme quoi le temps d'execution est depassé.

Voici un exemple de requete :

SELECT count(id) as total FROM table_1 WHERE (cat_juridique=1300 OR cat_juridique=1100 OR cat_juridique=1200) AND (cp BETWEEN '01' AND '09' ) AND (date_creation BETWEEN '19950101' AND '20001231') AND (ca='0' OR ca BETWEEN '1' AND '500000')

Pourtant les champs id, cat_juridique, cp, date_creation et ca sont indexés.
Ah tant que j'y pense je travaille avec PhpMyadmin (Apache/1.3.24 (Win32) PHP/4.2.0 => je comprends rien a ca mais si ca peut aider a voir mon environnement de travail)

Donc si qqn a une idee.... Ben merci d'avance =)

 

[French Fred]

Salut,

Il me semble que l'instruction between est quelque peu longue.
Quelques idees...


1/ "ca" a l'air d'etre une chaine, essaye de le convertir en en integer et utilise > et < ?
2/ date_creation essaye le format integer !


FRed

 

[mv_murph]

"ca" a l'air d'etre une chaine

ben non c un int sur 13 caracteres.

utilise > et <

en fait si j'ai mis des between c parce que j'avais lu dans un forum que c'etait mieux .. mais bon je confonds peut etre avec autre chose

Je vais deja essayer les "<" et ">" avant de toucher a la structure meme de ma table.

Merci !^

 

[French Fred]

ben non c un int sur 13 caracteres.

Supprime les quotes dans ce cas

 

[mv_murph]

Supprime les quotes dans ce cas

lol .. je l'ai fait en meme temps que j'ai mis les "<" et ">" =)
mais je me demandais du coup qd je mets les "<" et ">" , ca ne va pas poser pb pour une requete du genre :

SELECT count(id) as total FROM table_1 WHERE (cat_juridique='1300' OR cat_juridique='1100' OR cat_juridique='1200') AND (cp BETWEEN '01' AND '09' ) AND (date_creation BETWEEN '19950101' AND '20001231') AND [color=darkred](ca<=1 AND ca>=500000 OR ca>=1000000 AND ca<=2000000)[/color]

Est ce qu'il va bien m'envoyer ce qu'il y a entre 1 et 50000 ET entre 1000000 et 2000000 ?

 

[Xele]

Met des parentheses pour délimiter les AND, comme ca pas de confusion possible.

((ca<=1 AND ca>=500000) OR (ca>=1000000 AND ca<=2000000))

 

[cedber]

Sinon tu peux essayé une chose :
Tu executes plusieurs requetes au lieu d'une seule genre "SELECT id FROM table_1 WHERE (cat_juridique=1300 OR cat_juridique=1100 OR cat_juridique=1200)", puis "SELECT id FROM table_1 WHERE (cp BETWEEN '01' AND '09' )" puis "SELECT id FROM table_1 WHERE (date_creation BETWEEN '19950101' AND '20001231')" ....

Ensuite tu fais l'intersection des resultats (en PHP) : tu recupere les ID qui sont communs a chaque resultat et tu les comptes...


Je sais pas si c'est vraiment plus rapide mais ca surcharge moins le BDD (la charge est mieux repartie)

 

[mv_murph]

Mais en fait j'ai l'impression que c'est le between du cp qui pose pb.

Car lorsque j'ecris :
... AND (cp like '01%') ...

Le temps de reponse est long mais correct. Mais je peux pas mettre des "like 'xx%'" tout le temps :/

 

[sgaze]

Question fondamentale : tu as des index sur les colonnes cat_juridique, cp, date_creation, ca ?

Pour le reste, French Fred a raison je pense : ajouter des quotes à des dates ou des numériques doit probablement forcer mysql à faire des travaux de conversion inutiles.

Au niveau syntaxe, tu peux aussi essayer d'écrire

cat_juridique in (1300, 1100, 1200)

au lieu de (cat_juridique=1300 OR cat_juridique=1100 OR cat_juridique=1200)
[/code]

 

[sgaze]

Mais en fait j'ai l'impression que c'est le between du cp qui pose pb.

Car lorsque j'ecris :
... AND (cp like '01%') ...

Le temps de reponse est long mais correct. Mais je peux pas mettre des "like 'xx%'" tout le temps :/

Like '01%' ne sera pas trop long si tu as un index sur la colonne cp d'une part et si le nombre de valeurs dans cp commençant par 01 ne représente pas plus de 10-20% des enregistrements.

 

[Digit]

Ensuite tu fais l'intersection des resultats (en PHP)

Pas sûr, un tel tri peut plomber les perfs du serveur PHP. Mieux vaut laisser côté SGBD.
Il faudrait s'assurer que le format des champs est optimal. phpmyadmin effectue des préconisations à ce sujet quand on lui demande d'analyser une structure de table, car il identifie les valeurs min et max entre autres..

L'indexation de date_creation n'est pas judicieuse car tu risques d'avoir une valeur différente par enreg, donc ton index n'apporte rien (si, il gonfle le volume de ta base et allonge les accès). Je te conseillerais de convertir ce champs en TIMESTAMP.

 

[easy_zik]

Alors coté optimisation de MySql, je te conseille de lire la doc sur le sujet. Il y a une section qui lui est dédié.

Le mot magique a connaitre pour tester et optimiser tes requetes est EXPLAIN. Tu mets ca devant ta requète et tu peux voir quels index ont été utilisés etc...
Dit toi aussi que dès que tu utilises des combinaisons de OR et de AND, les index ne sont plus utilisables.
Essaye de voir si tu ne peux pas découper ta requète en plusieures petites requètes optimisées.
Ensuite, tu fais la somme des résultats. Mais ca peut etre extrêmement rapide...

Dit toi aussi que les type comptent enormement. cp devrait etre un entier et non une chaine, cat_juridique aussi : donc pas de quotes.

Au passage, puisque tu te posais la question, je t'affirme qu'il vaut mieux utiliser la commande BETWEEN que un > et un <
Ca alourdit moins la longeur de ta requète et c'est idem coté optimisation.

De la même façon, rien a voir avec l'optimisation non plus mais plutot que d'utiliser des OR qui ralonge la requète, utilise l'operateur IN :
WHERE cat_juridique IN (1300,1100,1200)
C'est vachement plus lisible...
(mais ca optimise pas plus que les OR, attention !!)


Bref pour conclure : segmente ta requète, verifie tes types, et utilise EXPLAIN pour bien savoir ce que fais MySql (et lis la doc )

 

[French Fred]

easyzik:

je t'affirme qu'il vaut mieux utiliser la commande BETWEEN que un > et un < .

Merci, c'est bon a savoir !!!

 

[mv_murph]

Merci easyzik

j'ai bien mis "WHERE cat_juridique IN (1300,1100,1200)"
et j'ai remis mes "between"
Je vais essaye le explain voir ce qu'il me dit

Pour ce qui est des types, en fait c'est pas moi qui avait créé la table, du coup sur les conseils que vous m'avez tous donnés je viens de jeter un coup d'oeil sur la structure, et ils y a bcp de int qui ont été changés en char(1) ou varchar ...
est ce que cela signifie que je dois tous les remettre ? (à mon avis la rponses est oui ;p )

en fait j'avais une seule grosse requete car je pensais que le fait de n'en avoir qu'une permettait d'optimiser, vu qu'il n'y avait plus qu'une requete ...
Bref je vais voir si je peux segmenter mais je ne suis pas sure

En tout cas merci encore

 

[mv_murph]

j'ai une question un peu bete mais tant que j'y suis ... =)

Si je remets des integers où ils auraient dû etre, pour les "between" et autres est-ce qu'il est encore necessaire que je mette les quotes ?

ex : BETWEEN '1' AND '500000'

//et pour la date je peux pas mettre de timestamp car elle n'est pas au format aaaa-mm-jj mais au format aaammjj :/

 

[easy_zik]

Si je remets des integers où ils auraient dû etre, pour les "between" et autres est-ce qu'il est encore necessaire que je mette les quotes ?

Oui si c'est des dates, non si c'est des nombres. Mais de toute facon, il n'y a qu'une seule conversion qui est effectuée au niveau du parsing de la requète. Donc les pertes sont quasi insignifiantes. Les quotes peuvent être un bon reflexe pour eviter les failles de Sql injection.
Ce qui est tres important au niveau des types, c'est quand tu fais des jontures sur des champs de type différents.... La tu perds toutes les optimisations possibles. Mais dans ton cas, tu n'utilises qu'une seule table... Donc pas de problème.

et pour la date je peux pas mettre de timestamp car elle n'est pas au format aaaa-mm-jj mais au format aaammjj

tu te crée une nouvelle colonne de type timestamp et tu la remplit avec les timestamp correspondant. Tu peux recréer tes dates avec des SUBSTRING :

UPDATE table_1 SET ma_nouvelle_date = TIMESTAMP( CONCAT( SUBSTRING(date_creation,0,4), '-', SUBSTRING(date_creation,4,2), '-', SUBSTRING(date_creation,6,2) ) )

Ou un truc dans le genre... à peut de chose près, koi tu vois ce que je veux dire...

Note: tu peux aussi choisir une colone de type int, à ce moment là, tu utilisera la fonction UNIX_TIMESTAMP() au lieu de TIMESTAMP()