Discussion :

[zaboug]

Bonjour,

j'ai un problème de performance avec une base de données : la requête me permettant d'obtenir toutes les informations dont j'ai besoin fait une jointure entre 7 tables différentes (utilisation de INNER JOIN) dont la taille varie entre 112 et 12 938 633 enregistrements (environ 2M d'enregistrements en moyenne).

voici la requête que ça donne :

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
SELECT DISTINCT p.hsvirtualtag_id
FROM biset_cont_tag b 
INNER JOIN hs_virtual_observed_tag v 
ON b.tagID = v.hsobservedtag_id 
INNER JOIN liste_prom p
ON p.hsvirtualtag_id = v.hsvirtualtag_id 
INNER JOIN prom_used u
ON p.promoter_id = u.promoter_id
INNER JOIN liste_TFBS t
ON u.promoter_id = t.promoter_id
INNER JOIN liste_mtx m
ON m.mtx_name = t.mtx_name
INNER JOIN transcription_factor TF
ON TF.TF_id = m.TF_id
WHERE bisetID = 1036
AND b.tagID != 401805
AND TF.gene_product = 'DBT'
AND contrainte = 1
AND upstream = 5000;

Cette requête doit être lancée pour chacune des 10675 lignes d'un fichiers. Sachant que le temps d'exécution de la requête varie entre 4 sec et 1 min 20 (sur une base de test dont chaque table contient 4 à 7 fois moins de données...), vous imaginez le temps total d'execution du script (si vous imaginez pas, ça prend plus de 48h...)

Je suis déjà aller sur le forum Perl, tout est optimisé de ce côté là. Maintenant, il faudrait que j'optimise la partie MySQL...

En regardant dans la doc, j'ai vu qu'il existait le partitionnement par intervalle qui pourrait peut être améliorer les perfs dans mon cas. Problème : ça existe à partir de la version 5.1 de MySQL et notre serveur est à la version 5.0 (et c'est pas moi qui m'occupe de ce serveur, ils hébergent notre base de données...).

Du coup, je me demandais si l'utilisation d'une vue pourrait améliorer le temps d'exécution dans mon cas. J'en ai jamais utilisé et je connais pas bien le principe, donc je sais pas si ça peut aller. J'ai pas trouvé de tuto sur les vues MySQL, ni de doc expliquant clairement le principe de ces vues. Du coup, si vous pouviez éclairer un peu ma lanterne en me donnant des liens vers de la doc qui m'aurait échappée ou des conseils, ça serait super cool

Merci d'avance

[Maljuna Kris]

Saluton,
En matière de performances sur une requête de sélection, le premier point à regarder c'est la présence et l'utilisation des index dans les jointures. Les gains de performances peuvent être très importants.
http://mysql.developpez.com/faq/?pag...miser_requetes
Ensuite c'est plutôt du côté de la configuration du serveur qu'il faut regarder, notamment de l'utilisation des caches.
http://mysql.developpez.com/faq/?pag...imiser_serveur
S'agissant de l'impact de l'utilisation de vues, j'ai un gros doute, mais, objectivement, je n'en sais rien.

[CinePhil]

Première chose à voir pour optimiser : est-ce que tout est correctement indexé ?

Cette requête doit être lancée pour chacune des 10675 lignes d'un fichier.

Pourquoi ne pas faire une jointure avec ce "fichier" (table ?) plutôt que de lancer 10 675 fois la requête avec un programme ?

Du coup, je me demandais si l'utilisation d'une vue pourrait améliorer le temps d'exécution dans mon cas.

Le problème c'est qu'on ne peut pas créer d'index sur une vue MySQL donc on perd un gros avantage.
Une autre piste serait de créer plutôt une table (temporaire ou non), laquelle peut ensuite être indexée. Mais si la table est grosse, l'ajout des index prend aussi du temps.
Approximativement combien de lignes doivent être retournées par la requête ?

DISTINCT est pénalisant.

Ce qui pénalise peut-être aussi, c'est la jointure entre m et t qui est basée sur la colonne mtx_name, probablement de type VARCHAR et c'est moins performant que les autres jointures basées sur des id probablement de type entier.

J'ai reconstitué le schéma d'après la requête.
De quelles tables viennent les colonnes contrainte et upstream ?

N'existe t-il pas un chemin plus court pour aller de b à TF en passant par p ?

Voilà quelques pistes.

[zaboug]

Première chose à voir pour optimiser : est-ce que tout est correctement indexé ?

Il y a une clé primaire dans chaque table si c'est ce que vous entendez par correctement indexé.

Pourquoi ne pas faire une jointure avec ce "fichier" (table ?) plutôt que de lancer 10 675 fois la requête avec un programme ?

En fait, le truc c'est que mon résultat final s'obtient en deux étapes : la première étape consiste à récupérer tous les bisetID répondant à certaines contraintes, c'est la que je génère le fichier de 10675 lignes. Voici la requête qui arrive à ce fichier :

Code :

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

1
2
3
4
5
6
7
8
SELECT DISTINCT bisetID,  g.gene_product, b.tagID
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 transcription_factor TF 
ON g.gene_product = TF.gene_product;

la deuxième étape est celle dont je parle dans mon premier post : je veux savoir si les bisetID que j'ai récupérer précédemment répondent aussi à d'autres contraintes. D'où la deuxième requête qui dépend des informations de la première :

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
SELECT DISTINCT p.hsvirtualtag_id
FROM biset_cont_tag b 
INNER JOIN hs_virtual_observed_tag v 
ON b.tagID = v.hsobservedtag_id 
INNER JOIN liste_prom p
ON p.hsvirtualtag_id = v.hsvirtualtag_id 
INNER JOIN prom_used u
ON p.promoter_id = u.promoter_id
INNER JOIN liste_TFBS t
ON u.promoter_id = t.promoter_id
INNER JOIN liste_mtx m
ON m.mtx_name = t.mtx_name
INNER JOIN transcription_factor TF
ON TF.TF_id = m.TF_id
WHERE bisetID = 1036 <= change à chaque ligne du fichier
AND b.tagID != 401805 <= change à chaque ligne du fichier
AND TF.gene_product = 'DBT' <= change à chaque ligne du fichier
AND contrainte = 1
AND upstream = 5000;

Et je lance le tout par un programme car les informations brutes sont traitées automatiquement par la suite (génération de fichiers de résultats, stats, identification...).

Le problème traité est assez complexe, j'espère ne pas totalement vous perdre en rentrant plus ou moins dans les détails...

Le problème c'est qu'on ne peut pas créer d'index sur une vue MySQL donc on perd un gros avantage.
Une autre piste serait de créer plutôt une table (temporaire ou non), laquelle peut ensuite être indexée. Mais si la table est grosse, l'ajout des index prend aussi du temps.
Approximativement combien de lignes doivent être retournées par la requête ?

La requête retourne entre 0 et je dirais une vingtaine de lignes au max (sachant que ce qui m'intéresse ici, c'est le nb de ligne retournées, et pas leur contenu)
J'allais justement me pencher sur la création d'une tables temporaire contenant le résultat de la jointure des 7 tables avec les conditions contrainte = 1 et upstream = 5000...

DISTINCT est pénalisant.

Ce qui pénalise peut-être aussi, c'est la jointure entre m et t qui est basée sur la colonne mtx_name, probablement de type VARCHAR et c'est moins performant que les autres jointures basées sur des id probablement de type entier.

J'ai reconstitué le schéma d'après la requête.
De quelles tables viennent les colonnes contrainte et upstream ?

N'existe t-il pas un chemin plus court pour aller de b à TF en passant par p ?

Voilà quelques pistes.

contrainte vient de la table liste_TFBS (t) et upstream vient de la table prom_used (u).

Et malheureusement, il n'existe pas de chemin plus court pour aller de b à TF...

[zaboug]

peut être que vous y verrez plus clair avec le schéma de la base (enfin, tout du moins la partie utilisée ici) :

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
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       |       |
+-----------+-------------+------+-----+---------+-------+
 
mysql> describe hs_virtual_observed_tag;
+------------------+--------+------+-----+---------+-------+
| Field            | Type   | Null | Key | Default | Extra |
+------------------+--------+------+-----+---------+-------+
| HsObservedTag_id | int(8) | NO   | PRI |         |       |
| HsVirtualTag_id  | int(8) | NO   | PRI |         |       |
+------------------+--------+------+-----+---------+-------+
dans ces deux tables, tagID = HsObservedTag_id
 
mysql> describe liste_prom;
+-----------------+--------+------+-----+---------+----------------+
| Field           | Type   | Null | Key | Default | Extra          |
+-----------------+--------+------+-----+---------+----------------+
| id              | int(8) | NO   | PRI | NULL    | auto_increment |
| hsvirtualtag_id | int(8) | NO   |     |         |                |
| promoter_id     | int(8) | NO   |     |         |                |
+-----------------+--------+------+-----+---------+----------------+
 
 
mysql> describe prom_used;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| promoter_id | int(8)  | NO   | PRI |         |       |
| tss         | int(20) | NO   |     |         |       |
| upstream    | int(5)  | NO   |     |         |       |
| downstream  | int(5)  | NO   |     |         |       |
+-------------+---------+------+-----+---------+-------+
 
mysql> describe liste_TFBS;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| TFBS_id     | int(8)      | NO   | PRI |         |       |
| promoter_id | int(8)      | NO   |     |         |       |
| mtx_name    | varchar(15) | NO   |     |         |       |
| contrainte  | int(1)      | NO   |     |         |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
 
mysql> describe liste_mtx;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(8)      | NO   | PRI | NULL    | auto_increment |
| TF_id    | int(8)      | NO   |     |         |                |
| mtx_name | varchar(15) | NO   |     |         |                |
+----------+-------------+------+-----+---------+----------------+
 
mysql> describe transcription_factor;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| TF_id         | int(8)      | NO   | PRI |         |       |
| transfac_name | varchar(15) | NO   |     |         |       |
| gene_product  | varchar(15) | NO   |     |         |       |
+---------------+-------------+------+-----+---------+-------+

EDIT : je viens de mettre le nez dans la doc MySQL à propos des indexs. Du coup, petite rectification : mes tables ne sont pas indexés (outre les index primary key).
Mais honnêtement, je m'y perds : faut il indexer les colonnes servant à la jointure, ou les colonnes de la clause where, ou les 2?
et dans le deuxième cas, il faudrait indexer toutes les colonnes utilisée dans la clause where?
je vais faire quelques tests...

[CinePhil]

Il y a une clé primaire dans chaque table si c'est ce que vous entendez par correctement indexé.

Et les clés étrangères utilisées dans les jointures de la requête sont-elles indexées ?

En fait, le truc c'est que mon résultat final s'obtient en deux étapes : la première étape consiste à récupérer tous les bisetID répondant à certaines contraintes, c'est la que je génère le fichier de 10675 lignes.

Donc si je comprends bien, c'est sur le résultat de la première requête :

Code :

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

1
2
3
4
5
6
7
8
SELECT DISTINCT bisetID,  g.gene_product, b.tagID
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 transcription_factor TF 
ON g.gene_product = TF.gene_product;

qu'on exécute la seconde :

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
SELECT DISTINCT p.hsvirtualtag_id
FROM biset_cont_tag b 
INNER JOIN hs_virtual_observed_tag v 
ON b.tagID = v.hsobservedtag_id 
INNER JOIN liste_prom p
ON p.hsvirtualtag_id = v.hsvirtualtag_id 
INNER JOIN prom_used u
ON p.promoter_id = u.promoter_id
INNER JOIN liste_TFBS t
ON u.promoter_id = t.promoter_id
INNER JOIN liste_mtx m
ON m.mtx_name = t.mtx_name
INNER JOIN transcription_factor TF
ON TF.TF_id = m.TF_id
WHERE bisetID = 1036 <= change à chaque ligne du fichier
AND b.tagID != 401805 <= change à chaque ligne du fichier
AND TF.gene_product = 'DBT' <= change à chaque ligne du fichier
AND contrainte = 1
AND upstream = 5000;

Il est donc possible de faire une jointure du résultat de la première requête sur la seconde. Cela donnera un résultat qui contiendra jusqu'à environ 200 000 lignes.

sachant que ce qui m'intéresse ici, c'est le nb de ligne retournées, et pas leur contenu

Donc avec un COUNT(DISTINCT hsvirtualtag_id) ça va réduire le nombre de lignes et ça devrait aussi être plus rapide.

Je remarque que, dans la première requête, la jointure entre g et TF est inutile puisqu'on ne retourne pas de colonne de TF, gene_product étant dans g. Et hop ! Une jointure de moins = du temps de gagné !

Comme j'ai du mal à voir la logique de la requête avec ses noms de tables et de colonnes obscurs pour moi, je vous laisse réfléchir à ces nouvelles pistes.

J'avais commencé cette requête mais je crains qu'il y ait un problème avec la dernière condition qui devrait peut-être être une condition de jointure avec une nouvelle instance de TF.

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
SELECT COUNT(DISTINCT p.hsvirtualtag_id)
FROM biset_cont_tag b 
INNER JOIN hs_virtual_observed_tag v  ON b.tagID = v.hsobservedtag_id 
    INNER JOIN liste_prom p ON p.hsvirtualtag_id = v.hsvirtualtag_id 
        INNER JOIN prom_used u ON p.promoter_id = u.promoter_id
            INNER JOIN liste_TFBS t ON u.promoter_id = t.promoter_id
                INNER JOIN liste_mtx m ON m.mtx_name = t.mtx_name
                    INNER JOIN transcription_factor TF ON TF.TF_id = m.TF_id
INNER JOIN (
    SELECT DISTINCT b2.bisetID,  g.gene_product, b2.tagID
    FROM biset_cont_tag b2
    INNER JOIN  hs_virtual_observed_tag h ON b2.tagID = h.hsobservedtag_id 
    INNER JOIN hs_transcript_go g ON h.hsvirtualtag_id = g.hsvirtualtag_id
) tmp ON b.bisetID = b2.bisetID AND b.tagID <> b2.tagID WHERE contrainte = 1
AND upstream = 5000
AND TF.gene_product = tmp.gene_product

Pour ce qui est des index, voir cet article.
D'une manière générale, on indexe :
- les clés étrangères, sauf la première clé d'une clé primaire dans une table associative ;
- les colonnes souvent interrogées dans les clauses WHERE ou de tri, sauf si elles ont très peu de valeur (inutile d'indexer les booléens).
Je dis ça de mémoire, ça fait un moment que je n'ai pas relu cet article.

[zaboug]

Et les clés étrangères utilisées dans les jointures de la requête sont-elles indexées ?

non, je crois pas, enfin pas selon la définition d'index que j'ai lu dans la doc.

Donc si je comprends bien, c'est sur le résultat de la première requête [...] qu'on exécute la seconde

oui, tu comprends bien.
sauf que c'est sur chaque ligne du résultat de la première que j'effectue la seconde

Donc avec un COUNT(DISTINCT hsvirtualtag_id) ça va réduire le nombre de lignes et ça devrait aussi être plus rapide.

je prends bonne note

Je remarque que, dans la première requête, la jointure entre g et TF est inutile puisqu'on ne retourne pas de colonne de TF, gene_product étant dans g. Et hop ! Une jointure de moins = du temps de gagné !

oui mais non : en effet, gene_product se trouve dans g, mais la jointure est nécessaire car seuls les gene_product presents dans TF m'intéresse.
En fait, dans TF on a un sous ensemble de gene_product qui ont une fonction particulière. C'est cette fonction qui m'intéresse ici, et elle n'est présente que dans la table TF. Bref, tout ça pour dire que je ne peux pas supprimer cette jointure...

Sur ce, je vais étudier la requête que tu proposes

En tout cas, merci de te pencher sur mon pb qui est loin d'être trivial (d'autant moins quand on connait pas le concepts biologiques qui se cachent dessous... )

[CinePhil]

(d'autant moins quand on connait pas le concepts biologiques qui se cachent dessous... )

Euh... des fois, il vaut peut-être mieux ne pas tout savoir... ça peut faire peur !

[zaboug]

J'ai commencé à indexer mes tables, et c'est fou comme un index peut rendre la requête soit beaucoup plus performante, soit beaucoup moins!
Je teste la deuxième requête (celle avec 7 jointures) toujours avec les mêmes données dans la clause where.
Sans index, ça prend 1min21
en mettant un index sur mtx_name dans les tables liste_TFBS et liste_mtx (les deux tables jointes sur mtx_name), le temps d'exécution passe à 8.32sec
par contre, j'ai testé un indexations d'autres champs et à chaque fois je perdais en temps d'exécution...

Du coup, je vais rester avec un index sur les champs mtx_name et point barre.

Maintenant, faut encore que je vois si en créant une table temporaire contenant les 7 jointures, je gagnerais pas du temps...

la suite au prochain épisode