Optimiser une requete qui prend une quarantaine de minute.

**zaboug** · 14/01/2009, 12h51

Bonjour,

je dois faire une requête complexe en MySQL sur des tables relativement volumineuses. Rien que la première partie de ma requête prend plus de 40 min à tourner en local...

Donc si vous pouviez m'aider à l'optimiser ça serait supra cool.

Pour cette requête, j'utilise 4 tables dont voici les DESCRIBE :

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
 
mysql> describe biset_cont_tag;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bisetID   | int(11)     | YES  | MUL | 0       |       | 
| tagID     | int(11)     | YES  |     | 0       |       | 
| discr_met | varchar(30) | YES  |     |         |       | 
| threshold | int(11)     | YES  |     | 0       |       | 
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> describe hs_virtual_observed_tag;
+------------------+--------+------+-----+---------+-------+
| Field            | Type   | Null | Key | Default | Extra |
+------------------+--------+------+-----+---------+-------+
| HsObservedTag_id | int(8) | NO   | PRI | NULL    |       | 
| HsVirtualTag_id  | int(8) | NO   | PRI | NULL    |       | 
+------------------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> describe hs_transcript_go;
+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| Id                   | int(11)     | NO   |     | 0       |       | 
| gene_product         | varchar(15) | YES  | MUL |         |       | 
| description          | mediumtext  | YES  |     | NULL    |       | 
| gi_ref               | int(11)     | YES  |     | 0       |       | 
| refseq               | varchar(20) | YES  |     |         |       | 
| HsVirtualTag_id      | int(11)     | YES  |     | 0       |       | 
| gene_product_aliases | text        | YES  |     | NULL    |       | 
+----------------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
 
mysql> DESCRIBE hs_transcription_factor;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| TF_id         | int(8)      | NO   | PRI | NULL    | auto_increment | 
| transfac_name | varchar(15) | NO   |     | NULL    |                | 
| gene_product  | varchar(15) | NO   |     | NULL    |                | 
| mtx_id        | int(8)      | NO   |     | NULL    |                | 
| core_position | int(2)      | NO   |     | NULL    |                | 
+---------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

et voici ma requete :

Code :

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

1
2
3
4
5
6
7
 
SELECT DISTINCT bisetID 
FROM biset_cont_tag, hs_virtual_observed_tag 
WHERE tagID = hsobservedtag_id 
AND hsvirtualtag_id IN (SELECT DISTINCT hsvirtualtag_id 
FROM hs_transcript_go AS gene, hs_transcription_factor AS TF 
WHERE gene.gene_product = TF.gene_product);

Cette requête a mis 41min a me renvoyer le résultat (sans le DISTINCT que j'avais oublié), et 47sec en limitant les résultats à 10 enregistrements.

Sachant que cette requête ne me permet que de récupérer la moitié de mon résultat, je panique un peu!

J'ai essayé en éliminant la sous requête au profit de deux jointures, mais c'est plus long (de même qu'en éliminant la jointure au profit d'une deuxième sous requête).

En cherchant sur le web, je vois qu'il est souvent question d'indexation pour optimiser les requêtes, mais je ne sais pas quel champs indexer ?

Tous les conseils seront les bienvenues.

Merci d'avance

**Maljuna Kris** · 14/01/2009, 13h45

Saluton,
Déjà, utilisons la bonne syntaxe de jointure :

Code :

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

1
2
3
4
5
6
7
8
SELECT DISTINCT bisetID 
FROM biset_cont_tag b
INNER JOIN  hs_virtual_observed_tag h
ON b.tagID = h.hsobservedtag_id 
WHERE H.hsvirtualtag_id IN (SELECT DISTINCT hsvirtualtag_id 
FROM hs_transcript_go g
INNER JOIN  hs_transcription_factor TF 
ON ge.gene_product = TF.gene_product);

Mais il faudra m'expliquer à quoi sert la deuxième jointure, celle de la sous-requête.

**zaboug** · 14/01/2009, 14h01

merci pour la syntaxe, ça faisait un bout de temps que j'avais pas fait de MySQL, ça part vite

mais j'avais pas le souvenir de cette syntaxe...

la deuxième jointure sert à filtrer les gene_product de la table hs_transcript_go : dans la table hs_transcription_factor, on a tous les gene_product correspondant à des facteurs de transcription et dans la tables hs_transcript_go, il y en a beaucoup plus.
Je ne veux que les hsvirtualtag_id correspondant à des facteurs de transcription, d'où la jointure.

je sais pas si c'est très clair. Je veux bien explicité d'avantage, mais le domaine d'application est assez pointu...

EDIT: j'ai testé avec la bonne syntaxe de jointure, ça gagne un dixième de seconde en limitant les résultats à 10...

**Maljuna Kris** · 14/01/2009, 14h17

Dans l'idéal il faudrait que les colonnes suivantes soient des index :
b.tagID
h.hsobservedtag_id
TF.hsvirtualtag_id
ge.gene_product
TF.gene_product
Normalement aussi, transformer la sous-requête en jointure devrait améliorer les performances.
En tout cas c'est ce qui est habituellement prescrit.

**zaboug** · 14/01/2009, 14h20

bon, alors question : est il possible d'ajouter des index à des tables qui sont déjà en production?

je m'explique, je bosse sur une base de données déjà en production, je dois lui ajouter des tables pour pouvoir répondre à de nouvelles requêtes (en autre celle que j'essaie d'optimiser).
Du coup, ajouter un index sur la table TF ne pose pas de problème (puisque c'est moi qui vient de la créer), mais pour les tables déjà en prod, est ce possible sans problème?
Y'a t-il une astuce pour pas tout faire planter?

**Maljuna Kris** · 14/01/2009, 14h28

MySQL est un SGBDR ACID, qui supporte les requêtes de description de données (LDD) en l'occurrence il s'agit de syntaxe de type ALTER TABLE.
Une conséquence possible de la création d'index est le ralentissement des requêtes INSERT, UPDATE, REPLACE et DELETE, par contre ça peut accélérer de nombreuses requêtes SELECT.
Avec des nuances suivant que les tables sont de type MyISAM ou InnoDB.
Comme toujours, il vaut mieux faire une sauvegarde de la base avant toute chose.

**zaboug** · 14/01/2009, 14h32

je suis en train d'essayer de transformer la sous requête en jointure avec ta syntaxe, mais j'ai du mal à visualiser : je vois pas comment on peut joindre plus de deux tables avec cette syntaxe... et j'arrive pas à trouvé ma réponse dans le site dev.mysql.com

**Maljuna Kris** · 14/01/2009, 14h38

Code :

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

1
2
3
4
5
6
7
8
SELECT DISTINCT bisetID 
FROM biset_cont_tag b
INNER JOIN  hs_virtual_observed_tag h
ON b.tagID = h.hsobservedtag_id 
INNER JOIN hs_transcript_go g
ON h.hsvirtualtag_id=g.hsvirtualtag_id 
INNER JOIN  hs_transcription_factor TF 
ON ge.gene_product = TF.gene_product;

Ça doit être qqe chose dans ce genre là, non ?

**zaboug** · 14/01/2009, 14h38

Envoyé par Maljuna Kris

MySQL est un SGBDR ACID, qui supporte les requêtes de description de données (LDD) en l'occurrence il s'agit de syntaxe de type ALTER TABLE.
Une conséquence possible de la création d'index est le ralentissement des requêtes INSERT, UPDATE, REPLACE et DELETE, par contre ça peut accélérer de nombreuses requêtes SELECT.
Avec des nuances suivant que les tables sont de type MyISAM ou InnoDB.
Comme toujours, il vaut mieux faire une sauvegarde de la base avant toute chose.

il s'agit de table MyISAM.
du coup, tu me conseilles de faire une sauvegarde de la base de données, puis de faire des ALTER TABLE pour ajouter les index genre:

Code :

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

ALTER TABLE hs_transcript_go ADD INDEX gene_product_index gene_product

et après, il suffit de faire les mêmes requêtes mais ça devraient être plus rapide (pour mes SELECT), c'est ça?

Je vais tester sur une copie de la base de données avant de le faire sur la base qui est en prod.

**zaboug** · 14/01/2009, 14h39

Envoyé par Maljuna Kris

Code :

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

1
2
3
4
5
6
7
8
SELECT DISTINCT bisetID 
FROM biset_cont_tag b
INNER JOIN  hs_virtual_observed_tag h
ON b.tagID = h.hsobservedtag_id 
INNER JOIN hs_transcript_go g
ON H.hsvirtualtag_id=g.hsvirtualtag_id 
INNER JOIN  hs_transcription_factor TF 
ON ge.gene_product = TF.gene_product;

Ça doit être qqe chose dans ce genre là, non ?

AH ok, on peut faire des INNER JOIN en cascade après un seul FROM. Merci beaucoup, je teste immédiatement

**Maljuna Kris** · 14/01/2009, 14h43

Attention il y avait une faute de frappe, un H. pour un h.
Mais apparemment tu m'as cité avant que je corrige mon post.

**zaboug** · 14/01/2009, 14h43

YAHOU! avec ta super requête pleine de jointure, je passe de 12sec64 à 1sec59 en limitant les résultats à 10!!

déjà rien que ça, c'est supra cool! (je vais pouvoir écrire des vrai jointure à présent, merci

)

bon, je vais quand même me pencher sur cette histoire d'index...