[MySQL] Optimisation d'une requête vraiment trop longue.

**GyZmoO** · 23/09/2009, 11h06

Bonjour à tous.

Je me tourne vers vous en tant que Non-expert de SQL.

Pour les besoins de notre projet nous voudrions exécuter une requête sur notre BD MySQL 4.1, mais cette requête prend un temps affreux (en fait, elle ne se termine même pas quand on lance...).

Bref je vous présente l'archi des tables :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
 
--
-- 1st Table
--
 
CREATE TABLE T_TABLE_1 (
       TABLE_1_ID           INTEGER NOT NULL,
       TABLE_1_MEASURE_DATE TIMESTAMP NOT NULL,
       TABLE_1_TYPE         VARCHAR(40) NULL,
       TABLE_1_USER		  VARCHAR(40) NULL,
       TABLE_1_CENTER_FREQ  DECIMAL(15,6) NOT NULL,
       TABLE_1_BANDWIDTH    DECIMAL(15,6) NOT NULL,
       TABLE_1_RBW          DECIMAL(15,6) NOT NULL,
       TABLE_1_VBW          DECIMAL(15,6) NOT NULL,
       TABLE_1_VBW_AUTO	  BOOLEAN NOT NULL,
       TABLE_1_RBW_AUTO	  BOOLEAN NOT NULL,
       TABLE_1_REF_LEVEL    FLOAT NOT NULL,
       TABLE_1_NB_DB_DIV    FLOAT NOT NULL,
       TABLE_1_TOTAL_POWER  FLOAT NOT NULL,
       TABLE_1_TOTAL_EIRP   FLOAT NOT NULL,
       TABLE_1_X_THRESHOLD  MEDIUMTEXT NULL,
       TABLE_1_Y_THRESHOLD  MEDIUMTEXT NULL,
       TABLE_1_TRACE_TYPE   INTEGER NOT NULL,
       TABLE_1_TRACE_COUNT  INTEGER NOT NULL,
       TABLE_1_Y_VAL        MEDIUMTEXT NULL,
       TABLE_1_XPOL_ID      INTEGER NOT NULL,
       LINKED_SPECTRUM_ID INTEGER NOT NULL,
       TABLE_1_ANSWER_TYPE  TINYINT(1) NOT NULL,
       TABLE_1_IS_REFERENCE BOOLEAN NOT NULL,
       SAT_CODE           VARCHAR(15) NULL,
       BEAC_NAME          VARCHAR(40) NULL,
       XPDR_NAME          VARCHAR(40) NULL,
       XPDR_TYPE		  INTEGER NULL,
       CXR_ID             INTEGER NULL,
       LK_ID			  INTEGER NULL,
       SITE_NAME          VARCHAR(40) NULL,
       MEAS_ANT_NAME      VARCHAR(40) NULL,
       RF_POLAR         TINYINT NULL,
       RF_TYPE			  TINYINT NULL,
       CHAIN_NAME         VARCHAR(40) NULL,
       TABLE_1_RESTORATION_DATE		TIMESTAMP NULL,
       TABLE_1_RESTORATION_USE		BOOLEAN NOT NULL DEFAULT FALSE
) TYPE=INNODB;
 
 
ALTER TABLE T_TABLE_1
       ADD  ( PRIMARY KEY (TABLE_1_ID) ) ;
 
ALTER TABLE T_TABLE_1 MODIFY COLUMN TABLE_1_ID INTEGER NOT NULL AUTO_INCREMENT;
 
CREATE INDEX IND_TABLE_1_DATE ON T_TABLE_1
(
       TABLE_1_MEASURE_DATE
);
 
--
-- 2nd Table
--
 
CREATE TABLE T_TABLE_2 (
       TABLE_2_ID           INTEGER NOT NULL,
       TABLE_1_ID           INTEGER NOT NULL,
       TABLE_2_CENTRAL_FREQ DECIMAL(15,6) NOT NULL,
       TABLE_2_BANDWIDTH    DECIMAL(15,6) NOT NULL
) TYPE=INNODB;
 
ALTER TABLE T_TABLE_2
       ADD  ( PRIMARY KEY (TABLE_2_ID) ) ;
 
ALTER TABLE T_TABLE_2 MODIFY COLUMN TABLE_2_ID INTEGER NOT NULL AUTO_INCREMENT;
 
ALTER TABLE T_TABLE_2
       ADD CONSTRAINT cons1 FOREIGN KEY (TABLE_1_ID )
                             REFERENCES T_TABLE_1(TABLE_1_ID) ;
 
--
-- 3rd Table
--
 
CREATE TABLE T_TABLE_3 (
	   TABLE_3_ID		  INTEGER NOT NULL,
	   TABLE_3_VALUE	  FLOAT NOT NULL,
	   PARTYP_NAME        VARCHAR(40) NOT NULL,
	   TABLE_2_ID           INTEGER NOT NULL
) TYPE=INNODB;
 
ALTER TABLE T_TABLE_3
	   ADD  (PRIMARY KEY (TABLE_3_ID	) );
 
ALTER TABLE T_TABLE_3 MODIFY COLUMN TABLE_3_ID INTEGER NOT NULL AUTO_INCREMENT;
 
ALTER TABLE T_TABLE_3
	   ADD CONSTRAINT cons2 FOREIGN KEY (TABLE_2_ID)
	   						 REFERENCES T_TABLE_2(TABLE_2_ID) ;

Voila pour la BD. Maintenant voici la requête :

Code :

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

1
2
3
4
5
 
 
DELETE FROM T_TABLE_3 WHERE 
TABLE_2_ID IN (SELECT TABLE_2_ID FROM T_TABLE_2 WHERE TABLE_1_ID 
IN (SELECT TABLE_1_ID FROM T_TABLE_1 WHERE TABLE_1_MEASURE_DATE BETWEEN DATE1 AND DATE2)

Voila, en gros, on essaye de supprimer tous les enregistrements de la table 3 qui correspondent à des enregistrements de la table 2 qui correspondant à des enregistrements de la table 1 qui répondent à la clause WHERE DATE BETWEEN DATE 1 & DATE 2.

Pour infos voici le nombre d'enregistrements dans les tables :

TABLE_1 = 350 000 lignes
TABLE_2 = 1 500 000 lignes
TABLE_3 = 7 500 000 lignes

Voila, je suis preneur de toutes critiques, remarques, idées...

Merci d'avance

**Maljuna Kris** · 23/09/2009, 11h44

Saluton,
Et pourqoui ne pas utiliser des jointures normalisées plutôt que des sous-requêtes ?

Code sql :

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

1
2
3
4
5
6
DELETE FROM T_TABLE_3 
INNER JOIN  TABLE_2 
ON TABLE_2.TABLE_2_ID=TABLE_3.TABLE_2_ID
INNER JOIN TABLE_1
ON TABLE_1.TABLE_1_ID=TABLE_2.TABLE_1_ID
WHERE TABLE_1_MEASURE_DATE BETWEEN DATE1 AND DATE2

**GyZmoO** · 23/09/2009, 14h45

Merci, je teste ça et je tiens au courant.

NB : dans la capture d'écran, la table T_SPECTRUM_TRACE correspond à la table T_TABLE_1, T_UNWANTED_CARRIER correspond à la table T_TABLE_2 et enfin T_UNWANTED_PARAMETER correspond à T_TABLE_3... Je me suis trompé dans le nom des tables

**GyZmoO** · 23/09/2009, 17h06

Alors tout d'abord, un grand merci !!

Nous sommes passé d'un vélo à une fusée.

Cependant, je ne comprend pas pourquoi le gain de temps est aussi énorme ?!

J'ai fait un explain sur la requête avec les inner join et je me rend compte que MySQL analyse bcp moins de ligne (avec ma requête il analysait 7 500 000 lignes, qui, je pense correspondent aux lignes de ma table T_TABLE_3) .

En gros, si j'ai compris, avec inner join, il commence par filtrer et garder uniquement les lignes correspondant à la clause WHERE DATE BETWEEN DATE 1 & DATE 2, c'est bien ça?

Alors qu'avec ma requête, il prenait toutes les lignes de la table T_TABLE_3 et il les analysait une à une, correct??

En tout cas merci beaucoup, c'est une belle épine enlevée !

**ced** · 24/09/2009, 11h58

La différence de temps vient du fait que MySQL n'optimise pas, mais vraiment pas, le traitement des sous-requêtes.
Il vaut donc mieux, autant que possible, privilégier les jointures.
Cela fait partie des améliorations apportées par la future version 5.4

.

ced