Discussion :

[dimainfo]

Bonjour à tous,
Les amis j'ai une table qui contient aux alentours de 25 millions de tuples et elle contiendra plus que ça au fil du temps, mais actuellement le temps de réponse est plus de 3min si je rajoute des critères de sélection.
Voici la structure de ma table et ma requête respectivement :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT (CASE WHEN (SELECT COUNT(j.JobID) FROM job j WHERE j.ParentJobID=jb.JobID )=0 THEN 0 ELSE 1 END) as ‘ExitSousJob’,jb.JobID as ‘JobID’ ,
jb.JobID as ‘DownFileIn’ , jb.JobID as ‘DownFileOut’,jb.JobID as ‘DownFileAll’ , jb.ParentJobID as ‘JobPereID’ , jb.StartDate as ‘DateDebut’ ,
(CASE WHEN jb.Duration IS NULL THEN ‘-‘ ELSE (CASE WHEN jb.Duration 0 THEN jb.Duration/1000 ELSE jb.Duration END) END ) as ‘Duree’ ,
Lab.Fr as ‘Statut’ ,
(CASE WHEN jbt.LabelID IS NULL THEN jbt.Alias ELSE (SELECT Fr FROM label l WHERE l.LabelID= jbt.LabelID) END) as ‘Type’ ,
(CASE WHEN jb.Description IS NULL THEN ‘-‘ ELSE jb.Description END ) as ‘Description’,
st.StatusTypeID as ‘IdStat’
FROM Job jb
INNER JOIN jobtype jbt on jb.JobTypeID=jbt.JobTypeID
INNER JOIN statustype st on st.StatusTypeID=jb.StatusTypeID
LEFT JOIN label lab on lab.LabelID = st.LabelID
WHERE jb.EnvID IN ( 1,40,41,22,37,25,26,39,38,42,43 ) AND jb.JobTypeID = 1 AND jb.StatusTypeID = 0
ORDER BY StartDate DESC LIMIT 500

Structure de la table :

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
 
CREATE TABLE IF NOT EXISTS `job` (
`JobID` int(10) unsigned NOT NULL,
`StartDate` datetime NOT NULL,
`EndDate` datetime DEFAULT NULL,
`Duration` int(10) unsigned DEFAULT NULL,
`ParentJobID` int(10) unsigned DEFAULT NULL,
`SessionJobID` int(10) unsigned DEFAULT NULL,
`JobTypeID` smallint(5) unsigned NOT NULL,
`StatusTypeID` mediumint(8) unsigned NOT NULL,
`Visible` tinyint(1) NOT NULL,
`EnvID` int(5) unsigned NOT NULL,
`Description` text,
PRIMARY KEY (`JobID`),
KEY `ParentJobID` (`ParentJobID`),
KEY `SessionJobID` (`SessionJobID`),
KEY `JobTypeID` (`JobTypeID`),
KEY `EnvID` (`EnvID`),
KEY `StatusTypeID` (`StatusTypeID`),
KEY `StartDate` (`StartDate`),
KEY `Duration` (`Duration`)
)

. Avez vous solution pour améliorer le temps de réponse ? Merci d'avance

[escartefigue]

Bonjour,

Cette façon de procéder n'est pas la meilleure, même si dans votre cas JOBID est une clef unique, il est préférable de faire un test d'existence (where exists) qu'un comptage (count(*))

Code :

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

(SELECT COUNT(j.JobID) FROM job j WHERE j.ParentJobID=jb.JobID )

Il manque un caractère dans ce bout de requête :

Code :

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

CASE WHEN jb.Duration 0 THEN jb.Duration/1000

Il faut que vous communiquiez le DDL de tables jointes et de leurs index pour que l'on puisse analyser les jointures

[dimainfo]

1. bien reçu.
2. j'ai du oublier ce caractere "="
3. ci-dessous la définition des tables jointes :

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS `jobtype` (
  `JobTypeID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `LabelID` int(10) unsigned DEFAULT NULL,
  `Alias` varchar(20) NOT NULL,
  PRIMARY KEY (`JobTypeID`),
  UNIQUE KEY `Alias` (`Alias`),
  KEY `LabelID` (`LabelID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS `statustype` (
  `StatusTypeID` mediumint(10) unsigned NOT NULL,
  `LabelID` int(10) unsigned DEFAULT NULL,
  `Alias` varchar(20) NOT NULL,
  PRIMARY KEY (`StatusTypeID`),
  UNIQUE KEY `Alias` (`Alias`),
  KEY `LabelID` (`LabelID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `environment` (
  `EnvID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ParentEnvID` int(10) unsigned NOT NULL,
  `Alias` varchar(20) NOT NULL,
  `Code` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`EnvID`),
  UNIQUE KEY `Alias` (`Alias`),
  KEY `ParentEnvId` (`ParentEnvID`),
  KEY `Code` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[escartefigue]

2. j'ai du oublier ce caractere "="

Plus probablement ">" ou "<" car, si c'est vraiment zéro, inutile de diviser par 1000

De plus, j'avais mal lu, dans ce code :

Code :

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

SELECT COUNT(j.JobID) FROM job j WHERE j.ParentJobID=jb.JobID

j.ParentJobID n'est pas unique, il est donc nettement préférable de remplacer le comptage par un where exists

Et aussi, ce prédicat de jointure n'est pas sargable :

Code :

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

INNER JOIN statustype st on st.StatusTypeID=jb.StatusTypeID

Car les colonnes ne sont pas définies avec la même longueur dans les 2 tables (8 / 10)

[dimainfo]

1. Vous avez raison le caractere est plutot : "<>"
2. Ci-dessous ma requete le caractere oublié + le where exists que vous suggérer :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT (CASE WHEN EXISTS (select jb.ParentJobID  from job jb where jb.JobID=jb.ParentJobID)  THEN 1 ELSE 0 END) as 'ExitSousJob',
jb.JobID as 'JobID' , jb.ParentJobID as 'JobPereID' , jb.StartDate as 'DateDebut' , 
 
(CASE WHEN jb.Duration IS NULL THEN '-' ELSE (CASE WHEN jb.Duration <> 0 THEN jb.Duration/1000 ELSE jb.Duration END) END ) as 'Duree' , 
 
lab.Fr as 'Statut' , 
(CASE WHEN jbt.LabelID IS NULL THEN jbt.Alias ELSE (SELECT Fr FROM label l WHERE l.LabelID= jbt.LabelID)  END) as 'Type' , 
(CASE WHEN jb.Description IS NULL THEN '-' ELSE jb.Description END ) as 'Description', st.StatusTypeID as 'IdStat' 
 
FROM Job jb   
INNER JOIN jobtype jbt on jb.JobTypeID=jbt.JobTypeID 
INNER JOIN statustype  st on st.StatusTypeID=jb.StatusTypeID 
LEFT JOIN label  lab on lab.LabelID=st.LabelID 
WHERE jb.EnvID IN ( 1,40,41,22,37,25,26,39,38,42,43 )   AND  jb.JobTypeID = 1 AND  jb.StatusTypeID = 0 ORDER BY StartDate DESC LIMIT 500

Sinon pour le type de la colonne StatusTypeID a été réctifié, c'etait juste parce que je faisais des tests dessus.
Par contre il n y'a toujours aucune amélioration de performances :/ , vous ne voyez pas d'ou pourra venir le probleme ?

[escartefigue]

Bonsoir,

Désolé, mais je n'aurai pas le temps ce soir d'investiguer, vous pouvez en attendant vérifier que
- toutes vos colonnes de jointures (join) ou de filtre (where) sont de même type et même longueur
- toutes vos colonnes de jointures ou de filtre correspondent à un index et ont au moins 8 à 10 valeurs distinctes
- vous ne faites pas de fonction dans vos prédicat de jointure ou de filtre (substr, digits, like %..., concat etc...)
- vous ne faites pas de jointure ou de filtre avec <> ou NOT... ou OR
- vous ne transportez que les colonnes utiles
- tous vos tests d'existence sont de type "where exists"
- tous vos tris sont nécessaire ou

[skuatamad]

Combien de ligne renvoie la requête au final (sans le LIMIT) ?

Si les filtres sont assez sélectif, créez un index sur le triplet (JobTypeID, StatusTypeID, EnvID) de la table job, voir peut être même rajouter startdate desc en 4eme colonne de l'index.
L'ordre des colonnes sera peut être à changer.

Code :

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

CREATE INDEX idx_job_type_statut_env ON job (JobTypeID, StatusTypeID, EnvID)

[dimainfo]

La requete me renvoie 526 175 lignes.
Je ne sais pas si ça sera une bonne idée de rajouter un index sur ce triplet ou pas du moment que je peux avoir une recherche sur la table job sans les critères jobType et StatusType.

[escartefigue]

La requete me renvoie 526 175 lignes.
Je ne sais pas si ça sera une bonne idée de rajouter un index sur ce triplet ou pas du moment que je peux avoir une recherche sur la table job sans les critères jobType et StatusType.

Le fait de créer un index sur 3 colonnes, C1+c2+C3, vous permet de rechercher sur tout ou partie des colonnes de cet index
Par exemple C1 seulement
Sous réserve bien sur que C1 seul soit discriminant

[aieeeuuuuu]

Bonjour,

Vous avez raison le caractere est plutot : "<>"

Alors, de la même façon, c'est tout à fait inutile.
Mais bon... ce point n'est certainement pas la source de vos problèmes de performance.


Si vous ne voulez au final que 500 lignes, alors vous pouvez filtrer plus en amont sur les 500 lignes qui vous intéressent dans la table des jobs :

remplacer

Code :

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

1
2
 
FROM Job jb

par

Code :

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

1
2
 
FROM (select [les colonnes nécéssaires] FROM Job ORDER BY StartDate DESC LIMIT 500) AS jb

[dimainfo]

@escartefigue
Donc si j'ai bien compris je rajoute l'index sur les 3 ou 4 attributs avec StartDate DESC et je supprime l'index sur le StartDate , JobTypeID, StatusTypeID et EnvID.

@aieeeuuuuu
Je ne sais pas si je filtre en amont les 500 lignes va etre bénéfique en terme de performance mais je vais l'essayer et je vous mettrai au courant.

Je vous remercie d'avance, je ferai ces rectifications et je reviendrai vers vous.

[escartefigue]

Attention, je précise, en reprenant mon exemple d'index sur les colonnes C1+C2+C3, vous pouvez utiliser cet index en recherchant :
- sur C1 seul
- sur C1+2
- sur C1+C2+C3

Mais ce même index n'est pas utilisable pour faire des recherches sur
- C2 seul
- C3 seul
- C2 + C3

A vous d'identifier les recherches fréquentes et de décider des index à créer en conséquences
Sans perdre de vue qu'un index n'a d'utilité que s'il porte sur des valeurs discriminantes. Par exemple, un index sur le code sexe qui ne peut prendre que 2, voire 3 valeurs, n'a absolument aucun intérêt, et pénalise même les perfs à cause des I/O inutiles qu'il engendre lors des insert/update

[dimainfo]

Bien reçu, je dois maintenant créer une table avec ces index et après la charger avec ma table de 40m de tuples vu que je ne peux pas créer un index dessus avec ce volume de données.

[dimainfo]

@aieeeuuuuu
Le filtre par 500 en amont est plus rapide par contre si les critères de recherche ne figurent pas sur ces 500 lignes alors le filtre va être faux.

[aieeeuuuuu]

en effet, il faut aussi remonter le filtre dans la sous requete.
Comme sur les deux jointure internes, il s'agit de clefs étrangères non nullables, ça ne devrait pas poser de problème/

donc :

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
 
SELECT ...
FROM (
    SELECT [les colonnes nécéssaires] 
    FROM Job 
    WHERE jb.EnvID IN ( 1,40,41,22,37,25,26,39,38,42,43 )   
    AND  jb.JobTypeID = 1 
    AND  jb.StatusTypeID = 0 
    ORDER BY StartDate DESC 
    LIMIT 500
) AS jb   
INNER JOIN jobtype jbt on jb.JobTypeID=jbt.JobTypeID 
INNER JOIN statustype  st on st.StatusTypeID=jb.StatusTypeID 
LEFT JOIN label  lab on lab.LabelID=st.LabelID 
ORDER BY StartDate DESC LIMIT 500

[dimainfo]

Si on remonte le filtre dans la sous requete ça donnera toujours le meme problème de performance d'ailleurs on faisant un test ça n'a aboutit en rien.
D'autres propositions ?

[aieeeuuuuu]

il faut bien entendu un index sur la date:

Code :

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

1
2
 
CREATE INDEX IX_JOB_DATE ON JOB(StartDate DESC,  JobTypeID, StatusTypeID , EnvID )

qui devrait couvrir la sous requete.

[dimainfo]

Après plusieurs test, j'ai rajouté un USE INDEX (StartDate) après le

Code :

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

FROM job

et ça a donnée une bonne résultat qui ne dépasse pas quand meme 1s, néanmoins le rajout d'un autre critere qui est

Code :

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

LOWER(Description)  LIKE '%22586339%'

ré-augmente mon temps de réponse à 4min

[escartefigue]

Ce qui est tout à fait normal, car un prédicat LIKE dont l'argument commence par % n'est pas sargable

Si vous devez faire des recherches fréquentes sur ce type d'argument avec des tables à forte volumétrie, il faut vous pencher sur les index full text

[dimainfo]

Le fullText ne marchera pas avec ce que je demande, car avec le MATCH ... AGAINST... je ne peux pas avoir l'enregistrement qui contient le mot '%TEST%' comme le LIKE.