Discussion :

[tomaprice]

Bonjour à tous,

J'ai crée une base de données de ~ 2GiB sous Mariadb (InnoDB). Cette dernière contient les tables suivantes :

oncodb_gene (~56 425 rows)
avec comme index :
id

oncodb_copynumberalteration (~ 12 116 669 rows)
avec comme index :
id
gene_id_id
sample_id_id

oncodb_variant (~534 140 rows)
avec comme index :
id
gene_id_id
sample_id_id
chrid
avis
comment

oncodb_sample (~ 554 rows)
avec comme index :
id
trial_id


J'ai besoin d'effectuer une jointure entre ces 4 tables, et mon problème vient du fait que la requête pour réaliser cette dernière est extrêmement longue (~ 15 minutes)

la requête en question :

Code :

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

1
2
3
4
5
6
SELECT * 
FROM oncodb_copynumberalteration 
INNER JOIN oncodb_gene on oncodb_copynumberalteration.gene_id_id=oncodb_gene.id and oncodb_gene.pdf=1 
INNER JOIN oncodb_sample on oncodb_sample.id=oncodb_copynumberalteration.sample_id_id and oncodb_sample.trial_id=2 
LEFT JOIN oncodb_variant on oncodb_copynumberalteration.gene_id_id=oncodb_variant.gene_id_id and oncodb_copynumberalteration.sample_id_id=oncodb_variant.sample_id_id and oncodb_variant.comment != 'COMMON SNP |' and oncodb_variant.comment != 'COMMON SNP | COSMIC |' and oncodb_variant.comment != 'COMMON SNP | COSMIC HOTSPOT |' and oncodb_variant.avis != '' 
WHERE ((oncodb_copynumberalteration.alteration_type = 'Amp' or oncodb_copynumberalteration.alteration_type = 'Del') or oncodb_variant.avis != '')

J'ai essayé des variantes mais sans succès

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 GENE.symbol,alteration_type,trial_id,seq_name,cgh_name,pdf,avis FROM
(
	SELECT * FROM oncodb_gene
	WHERE oncodb_gene.pdf=1
) as GENE 
INNER JOIN 
(
	SELECT gene_id_id,alteration_type,trial_id,oncodb_copynumberalteration.sample as cgh_name, sample_id_id FROM oncodb_copynumberalteration INNER JOIN oncodb_sample on oncodb_sample.id=oncodb_copynumberalteration.sample_id_id WHERE oncodb_sample.trial_id=4
) as CNA
on GENE.id=CNA.gene_id_id
LEFT JOIN
(
	SELECT seq_name,avis,comment,gene_id_id,sample_id_id FROM oncodb_variant INNER JOIN oncodb_sample on oncodb_sample.id=oncodb_variant.sample_id_id WHERE oncodb_sample.trial_id=4 and oncodb_variant.comment != 'COMMON SNP |' and oncodb_variant.comment != 'COMMON SNP | COSMIC |' and oncodb_variant.comment != 'COMMON SNP | COSMIC HOTSPOT |' and oncodb_variant.avis != ''
) as VARIANT
on GENE.id=VARIANT.gene_id_id and CNA.sample_id_id = VARIANT.sample_id_id
WHERE ((alteration_type = 'Amp' or alteration_type = 'Del') or avis != '')

Il y a t-il une possibilité d'optimiser les choses selon vous ou est ce que je dois me résoudre à ne pas faire cette jointure.

[escartefigue]

oncodb_copynumberalteration (~ 12 116 669 rows)
avec comme index :
id
index gene_id_id
sample_id_id

Bonsoir

Ce n'est pas clair en ce qui concerne ce ou ces index sur la table la plus volumineuse
Si vous avez un seul index et qu'il commence par la colonne id, alors vos critères de jointure outer ne sont pas sargable
Qu'en est il ?

[tomaprice]

Bonsoir

Ce n'est pas clair en ce qui concerne ce ou ces index sur la table la plus volumineuse
Si vous avez un seul index et qu'il commence par la colonne id, alors vos critères de jointure outer ne sont pas sargable
Qu'en est il ?

Désolé, je voulais écrire

oncodb_copynumberalteration (~ 12 116 669 rows)
avec comme index :
id
gene_id_id
sample_id_id

[escartefigue]

Créez un index sur la table oncodb_copynumberalteration
avec (gene_id_id + sample_id_id) seulement
Index requis pour votre jointure outer

[Artemus24]

Salut tomaprice.

J'ai un mieux présenté votre requête que voici :

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
SELECT      * 
      FROM  oncodb_copynumberalteration  as t1
 
INNER JOIN  oncodb_gene                  as t2
        on  t2.id            = t1.gene_id_id
       and  t2.pdf           = 1 
 
INNER JOIN  oncodb_sample                as t3
        on  t3.id            = t1.sample_id_id 
       and  t3.trial_id      = 2 
 
 LEFT JOIN  oncodb_variant               as t4
        on  t4.gene_id_id    = oncodb_copynumberalteration.gene_id_id
       and  t4.sample_id_id  =  t1.sample_id_id
       and  t4.comment      != 'COMMON SNP |' 
       and  t4.comment      != 'COMMON SNP | COSMIC |' 
       and  t4.comment      != 'COMMON SNP | COSMIC HOTSPOT |' 
       and  t4.avis         != ''
 
WHERE t1.alteration_type in ('Amp', 'Del'
   OR t4.avis != '';

Est-ce que dans votre table "oncodb_variant", la colonne avis, est-elle à vide ou pas ?
Si la réponse est non, alors le test : "and t4.avis != ''" est sans effet.
Seul le test dans le where aura un impact sur vos lignes.

Voici ce que je conseille comme index :

pour oncodb_copynumberalteration :
--> primary key sur id
--> index sur gene_id_id
--> index sur sample_id_id
--> index sur (id ; alteration)

pour oncodb_gene :
--> primary key sur id
--> index sur (id ; pdf)

pour oncodb_sample :
--> primary key sur id
--> index sur (id ; trial_id)

pour oncodb_variant :
--> primary key sur id
--> index sur (gene_id_id ; sample_id_id ; comment ; avis).

@+

[tomaprice]

Merci beaucoup pour votre aide, j'ai suivi vos suggestions et cela a grandement amélioré la rapidité d’exécution de la requête, ~ 25 secondes au lieu de 15 minutes.
Je pense avoir compris la logique de création des index au travers de cet exemple.