Optimiser mon traitement de données

**glsn** · 10/05/2006, 12h32

Bonjour,
Je vous explique mon petit problème.
Alors voila, j'ai une table de membres. De cette table j'extrais environ 20.000 enregistrements.
Pour chacun de ces 20.000 membres, je vais chercher les personnes qui font partie de leur carnet d'adresse.

Ensuite pour chacune de ces personnes du carnet d'adresse (maximum 10 par membre) je fais une boucle qui permet d'extraire les messages postés par chaque personne dans le forum A ou le forum B et une deuxième boucle permettant d'extraire les messages postés par chaque personne dans le forum C ou D.

On pourrait schématiser ainsi:

Sélection des 20.000 membres => (G1)
---> Extraction des personnes qui appartiennent au carnet d'adresse pour chaque membre de (G1) => (G2)
------> Extraction des messages postés sur le forum A ou sur le forum B
pour les membres de (G1) => (G3)
------> Extraction des messages postés sur le forum C ou D pour les membres de (G1) => (G4)

Ensuite on affiche les informations dans un tableau du style
Id membre (G1) | Membres du carnet d'adresse (G2) | Messages (G3) | Messages (G4)

Donc j'ai testé pour 500 membres (G1) la requête mets 269 secondes à s'exécuter et j'en ressort 129 messages (55 de G3 et 74 de G4)

Je voulais savoir comment optimiser tout cela sachant qu'à terme ce ne sera plus 500 mais 20.000 membres minimums.

Je précise que cette requête sera exécutée une fois par jour en CRON.

Merci d'avance.

**Fladnag** · 10/05/2006, 13h15

Si ta base est assez solide, je te conseille de remplacer les boucles par une seule requete avec des jointures directement.

Sinon, remplace les requetes executées dans les boucles pour chaque personne par la construction d'une requete avec quelque chose comme : Utilisateur.ID IN(....*liste des ID des utilisateurs*...) pour faire une seule requete a la fin.
Eventuellement gerer un tableau indexé par les ID d'utilisateurs pour retrouver facilement les infos dont tu as besoin.

Enfin, si tu dois faire plus d'une requete et que la 1ere est la plus longue, tu peux commencer a faire le traitement avant qu'elle ait fini de s'executer grace a http://fr3.php.net/manual/fr/function.mysql-unbuffered-query.php. (Je te conseille aussi de lire les notes, certaines sont interessantes)

**ePoX** · 10/05/2006, 13h19

Donc j'ai testé pour 500 membres (G1) la requête mets 269 secondes

Elle à quelle tete ta requete ? Parceque bon 20 000 lignes c'est peanut normalement, même pour sqlite.

**kalash_jako** · 10/05/2006, 13h28

Avant, j'avais créé un jeu en ligne avec un plateau de 100 cases sur 100 cases.

Je fesais donc une requètes avec le retournement des 10 000 cases et en plus, j'ajoutais des infos sur la cases, le propriétaire etc...

Avant optimisation, le script était en timeout. (boucle executée 10 000 fois, une requète SQL à chaque fois

)

Après, 1seconde à peine!

Pour cela, la construction d'une seule requète est indispensable!
(Jointure...)

**glsn** · 10/05/2006, 13h48

Merci pour vos réponses.

Je vais essayer d'optimiser tout ça et je reviends vers vous si c'est toujours aussi long.

Sinon ma requête qui extrait les membres au départ (500 puis par la suite 20.000) met 1.01 secondes à s'exécuter sous PHPmyAdmin.

**glsn** · 10/05/2006, 15h09

J'ai modifié mes requêtes, dorénavant je n'en ai plus que 2 au lieu de 4.

Donc de ma table contenant environ 60.000 enregistrements, j'en sélectionne 2000 (à terme il y en aura 25.000 minimums d'extraits).
Je cherche les correspondances de ces 2.000 membres dans ma deuxième requête.
Il en ressort 600 correspondances.

Et la page a mis 283 secondes à se charger. Mais le script ne s'est pas interrompu.

Sachant que cela sera exécuté en CRON, je n'aurais pas de contrôle direct sur l'application lorsqu'elle s'exécutera chaque nuit.
Comment faire pour être sur que ma requête s'exécute sans se bloquer ?
Car à vue d'oeil il faudra 56 minutes pour l'exécuter ! Cela est énorme

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT DISTINCT id_trajet, id_region_d, id_dept_d, id_region_a, id_dept_a,
status, id_membre, id_ville_d, id_ville_a, date_depart, id_frequence
FROM textes
WHERE ((id_dept_d = 78 AND id_dept_a = 83 AND id_ville_d <> id_ville_a)
OR (id_ville_d = 19094 AND id_ville_a = 34113)) 
AND textes.id_membre <> 1 AND textes.date_entree = DATE_ADD(SYSDATE(), INTERVAL - 1 DAY) 
AND (date_depart >= DATE_SUB('2006-05-18', INTERVAL 1 DAY )) 
AND (date_depart <= DATE_ADD('2006-05-18', INTERVAL 1 DAY)) 
AND (id_frequence = 6)

La table "textes" contient 31.000 enregistrements environ.
Voyez vous une amélioration qui pourrait me faire gagner beaucoup de temps ?

Merci

**hugo123** · 10/05/2006, 16h18

Je pose la question par acquis de conscience, tes tables ont elles des index ?

Peux tu tenter avec phpmyadmin de faire

EXPLAIN SELECT ... (ta requete);

Tu devrais avoir des infos intéressantes, entre autre les endroits ou ca coince le plus.

**glsn** · 11/05/2006, 09h26

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
EXPLAIN  SELECT  DISTINCT id_trajet, id_region_d, id_dept_d, id_region_a, id_dept_a, 
STATUS , id_membre, id_ville_d, id_ville_a, date_depart, id_frequence
FROM textes
WHERE ((id_dept_d =78
AND id_dept_a =83
AND id_ville_d <> id_ville_a
)
OR (id_ville_d =19094
AND id_ville_a =34113
)
)
AND textes.id_membre <>1
AND textes.date_entree = DATE_ADD( SYSDATE(  ) ,  INTERVAL  -1
DAY  ) 
AND (date_depart >= DATE_SUB(  '2006-05-18',  INTERVAL 1 
DAY  ) 
)
AND (date_depart <= DATE_ADD(  '2006-05-18',  INTERVAL 1 
DAY  ) 
)
AND (id_frequence =6
)

**hugo123** · 11/05/2006, 10h07

Tu n'as pas tout copier coller, il semble qu'il manque un bout.
Mais au vu de ce que tu as déjà mis, il semble que tu as oublié de mettre des index. En effet tu fais un scan complet de tous les enregistrements bien qu'il existe des clauses qui devraient restreindre la recherche.

Pour tester, poses une index sur :
id_dept_d
id_ville_d
id_ville_a
id_membre
date_entree
date_depart
id_frequence

et retest ton temps de requete.

ps : evidemment tu n'auras pas forcément besoin de tout ces index, mais ne connaissant pas ton modèle, je préfère de les faire tous poser pour l'instant.
Quand tu poseras un index, tu obtiendras aussi les cardinalités de chaque valeur, exemple, id_frequence a 200 occurences distinces.
L'idéal étant d'avoir des index sur les colonnes les plus discriminantes. Il est souvent inutile d'avoir un index sur une colonne dont toutes les valeurs sont différentes (a moins que l'objectif soit de poser un index unique pour interdire un doublon).

**Fladnag** · 11/05/2006, 10h24

Envoyé par hugo123

L'idéal étant d'avoir des index sur les colonnes les plus discriminantes. Il est souvent inutile d'avoir un index sur une colonne dont toutes les valeurs sont différentes (a moins que l'objectif soit de poser un index unique pour interdire un doublon).

Heu... justement, il me semblait que poser un index sur un champ ID d'une table par exemple était utile... a condition evidemment de faire en sorte que la majorité des requetes executées soit formatées de la sorte WHERE ID= ... ou WHERE ID IN (...).

En fait, pour moi un index devrait etre positionné sur les champs les plus utilisés dans les requetes (par exemple pour une liste de personnes, un index sur le nom parait approprié, tandis qu'un index sur le prénom parait plus inutile (evidemment, ca depend des applications derriere))

**hugo123** · 11/05/2006, 10h32

Heu... justement, il me semblait que poser un index sur un champ ID d'une table par exemple était utile... a condition evidemment de faire en sorte que la majorité des requetes executées soit formatées de la sorte WHERE ID= ... ou WHERE ID IN (...).

Tout à fait d'accord, j'ai listé justement les champs qu'il a utilisé dans sa clause WHERE.
Poser un index sur une colonne jamais utilisé dans un WHERE ne sert pas.
Et il ne faut pas oublier que les index sont couteux lors des inserts.

Je ne sais pas ou j'avais la tête quand j'ai écrit ca :

Il est souvent inutile d'avoir un index sur une colonne dont toutes les valeurs sont différentes

C'est utile puisque la recherche ne fera pas de full scan, les index étant triés.

Pas bien le matin moi ^^

**glsn** · 11/05/2006, 10h42

Ne connaissant rien aux index, auriez vous un tutorial afin que je vois quelles colonnes peuvent être indexées et comment faire appel à cette indexation ?

Merci d'avance pour votre aide

Edit:
J'ai parcouru un tutorial de Developpez et maintenant je me demande sur quelles colonnes créer mes index. Si je dois faire un index sur plusieurs colonnes ou plusieurs index.
Les champs les plus sollicités sont id_ville_d, id_ville_a, date_depart, id_dept_d et id_dept_a.
Dois je créer un index pour chacune de ces 5 colonnes ou un index regroupant plusieurs de ces colonnes ?

Edit 2:
La table Textes a environ 500 INSERT par jour.

Merci d'avance

**hugo123** · 11/05/2006, 11h02

http://dev.mysql.com/doc/refman/5.0/...imization.html

http://dev.mysql.com/doc/refman/5.0/...structure.html

Tu verras que ca peut te faire gagner un temps appréciable ^^

Si tu veux déjà tester de facon rapide, poses les index que je t'ai dit a partir de phpmyadmin. Sur la page structure, tu as l'option.

**Mr N.** · 11/05/2006, 11h17

Et surtout, chose à ne pas oublier : ANALYZE TABLE ma_table

A faire régulièrement et surtout quand les index changent. Le mieux est de le faire sur toute les tables...

**glsn** · 11/05/2006, 11h33

Un GRAND merci à tous pour votre aide et vos conseils.
Cela m'a effectivement permis de réduire énormément les temps de traitement.

Encore

**hugo123** · 11/05/2006, 11h46

Par curiosité, on pourrait avoir un ordre d'idée en chiffre ?

**glsn** · 11/05/2006, 11h48

Dorénavant il faut 111 secondes pour exécuter 26.000 membres alors qu'avant il en fallait autant pour 400 membres.

**Mr N.** · 11/05/2006, 11h55

Je ne sais pas ce que tu fais avec ces 26'000 membres, mais je trouve que c'est encore beaucoup.
Pourrais-tu nous remettre un explain de ta requête ?

**glsn** · 11/05/2006, 13h40

Ma requête de départ sélectionne 15.000 membres environ sur un total de 60.000

Ensuite pour chaque membres j'extrait les textes qui correspondent au critère. Donc sur 30.000 textes j'en extrais 5.700 environ.

Pour ce qui est de l'Explain, le résultat est: (désolé on peut pas mettre de tableau ici)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
table | type | possible_keys | key | key_len | ref | rows | Extra
textes | range | id_membre,id_dept_d,id_ville_d,id_ville_a,id_dept_... | date_depart | 3 | NULL | 1 | Using where; Using temporary
membres | eq_ref | PRIMARY,id_membre,id_membre_2 | PRIMARY | 4 | textes.id_membre | 1 | 
frequence | eq_ref | PRIMARY | PRIMARY | 4 | trajets.id_frequence | 1 | Using where
villes_d | eq_ref | PRIMARY,id_ville | PRIMARY | 4 | trajets.id_ville_d | 1 | Using where
villes_a | eq_ref | PRIMARY,id_ville | PRIMARY | 4 | trajets.id_ville_a | 1 | Using where
dept_d | eq_ref | PRIMARY,id_dept | PRIMARY | 4 | trajets.id_dept_d | 1  | Using where
dept_a | eq_ref | PRIMARY,id_dept | PRIMARY | 4 | trajets.id_dept_a | 1 | Using where

**hugo123** · 11/05/2006, 14h46

Pour la signification de ton tableau :

http://dev.mysql.com/doc/refman/5.0/fr/explain.html

Au vu de ton explain plan, je suppose que la requête joué n'est pas celle que tu nous avais mis plus haut mais qu'elle comporte maintenant les jointures avec les autres tables (membres, frequence, ville_d, ville_a, dept_d, dept_a) plutot que des valeurs fixés en dur.

Tu as bien mis tes id_xx en PRIMARY KEY dans leurs tables de références associés ?

D'après la doc, lorsqu'on fait une jointure, pour optimiser la requête, les champs doivent être de même type exactement et de même taille, est ce le cas ?

C'est son premier passage en range par la date qui me plait pas trop.
T'as fait un ANALYZE table sur toutes tes tables avant de lancer ta requête ?

Optimiser mon traitement de données

Requêtes MySQL

Discussions similaires

Partager

Partager