Discussion :

[mickael.camelot]

Bonjour,

Je cherche à optimiser une requête avec Jointure et j'aimerai avoir un avi.

La requête suivante s'exécute en 0.10 sec sur un jeu de données réduit, en revanche sur un jeu de données plus conséquent (2500000 LR_DESTINATION, 8000000 LR_DESTINATION_RUBRIQUE_GENRIQUE) le temp d'exécution dépasse bien souvent 1sec.

Objectif :
-1 Sélectionner l'ensemble des 'ID', depuis une table 'LR_DESTINATION',
dont l'ID de la 'LR_DESTINATION' est référencé dans la table 'LR_DESTINATION_RUBRIQUE_GENERIQUE' et dont les ID_RUBRIQUE appartiennent à l'ensemble (1,2,3,4,5,6,7).
-2 Ces 'ID' sélectionnés ne doivent pas être mémorisé au préalable, donc être absent de la table 'DESTINATION_MEMORY'
-3 Ces 'ID' sélectionnés seront ensuite filtrés sur leur URL et leur DONE.

Ma Requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT DISTINCT * FROM `LR_DESTINATION` LR
   LEFT JOIN DESTINATION_MEMORY DM
     ON DM.LR_DESTINATION_ID = LR.ID
   JOIN (SELECT DISTINCT LR_DESTINATION_ID
   	   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
          WHERE ID_GENERIQUE IN (20,21,22,23,24,25,26,27,28,29)) AS RUBJOIN
	 ON RUBJOIN.LR_DESTINATION_ID = LR.ID
  WHERE DONE = 0
    AND URL_SEO <> '/pros/url61234'
	AND ID_DEPARTEMENT = 'D090'
	AND DM.LR_DESTINATION_ID IS NULL
  LIMIT 0,25;

Ma Base de données

Table LR_DESTINATION

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
 
CREATE TABLE `lr_destination` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `URL_SEO` varchar(255) NOT NULL,
  `ID_LOCALITE` varchar(8) NOT NULL,
  `ID_DEPARTEMENT` varchar(4) NOT NULL,
  `ID_REGION` varchar(4) NOT NULL,
  `TITRE` varchar(255) NOT NULL,
  `NOMBRE_LIENS_MIS` int(11) NOT NULL,
  `NOMBRE_LIENS_MAX` int(11) NOT NULL,
  `DONE` tinyint(4) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `DONE` (`DONE`),
  KEY `URL_SEO` (`URL_SEO`),
  KEY `ID_DEPARTEMENT` (`ID_DEPARTEMENT`),
  KEY `ID_LOCALITE` (`ID_LOCALITE`),
  KEY `ID_REGION` (`ID_REGION`)
) ENGINE=InnoDB AUTO_INCREMENT=109109 DEFAULT CHARSET=latin1;

Table LR_DESTINATION_RUBRIQUE_GENERIQUE

Code :

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

1
2
3
4
5
6
 
CREATE TABLE `lr_destination_rubrique_generique` (
  `LR_DESTINATION_ID` bigint(20) NOT NULL,
  `ID_GENERIQUE` int(8) NOT NULL,
  PRIMARY KEY (`LR_DESTINATION_ID`,`ID_GENERIQUE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table DESTINATION_MEMORY

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE `destination_memory` (
  `LR_DESTINATION_ID` int(11) NOT NULL,
  `TITRE` int(11) NOT NULL,
  `NOMBRE_LIENS_MIS` int(11) NOT NULL,
  `NOMBRE_LIENS_MAX` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1

Explication:
- J'utilise un LEFT JOIN sur une table MEMORY pour éviter de faire un NOT IN (x,y,z)
- J'utilise un dans le JOIN un DISTINCT car plusieurs LR_DESTINATION_ID identiques peuvent remonter. Il s'agit d'une table de liaison entre des DESTINATIONS et des RUBRIQUES.
- Le DONE, URL_SEO, ID_DEPARTEMENT sont des discriminant de filtrage pour la sélection

Explain

Code :

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

1
2
3
4
5
6
7
8
9
 
+----+-------------+-----------------------------------+--------+-------------------------------------+---------+---------+---------------------------+--------+--------------------------+
| id | select_type | table                             | type   | possible_keys                       | key     | key_len | ref                       | rows   | Extra                    |
+----+-------------+-----------------------------------+--------+-------------------------------------+---------+---------+---------------------------+--------+--------------------------+
|  1 | PRIMARY     | <derived2>                        | ALL    | NULL                                | NULL    | NULL    | NULL                      |  54823 | Using temporary          |
|  1 | PRIMARY     | LR                                | eq_ref | PRIMARY,DONE,URL_SEO,ID_DEPARTEMENT | PRIMARY | 8       | RUBJOIN.LR_DESTINATION_ID |      1 | Using where              |
|  1 | PRIMARY     | DM                                | ALL    | NULL                                | NULL    | NULL    | NULL                      |     25 | Using where; Not exists  |
|  2 | DERIVED     | LR_DESTINATION_RUBRIQUE_GENERIQUE | index  | NULL                                | PRIMARY | 12      | NULL                      | 147821 | Using where; Using index |
+----+-------------+-----------------------------------+--------+-------------------------------------+---------+---------+---------------------------+--------+--------------------------+

On voit que la dernière requête effectue un FULL SCAN sur la table LR_DESTINATION_RUBRIQUE_GENERIQUE, 147821 correspondant exactement au nombre d'enregistrements dans cette table

[punkoff]

bonjour,

Assurez-vous d'avoir l'index :
LR_DESTINATION_RUBRIQUE_GENERIQUE (ID_GENERIQUE, LR_DESTINATION_ID)

Ensuite j'essaierai de passer par un test d'existance (EXISTS) pour la table LR_DESTINATION_RUBRIQUE_GENERIQUE au lieu de passer par une jointure vu que vous n'utilisez aucune colonne de cette table dans le select.

A part ça je ne vois pas bien d'autre solution.

[mickael.camelot]

Bonjour punkoof,

merci de votre réponse,

L'utilisation du EXISTS à la place du JOIN fonctionne, cependant mysql ram énormément.
je ne vois pas comment écrire la requête différemment.

JOIN : 0.9 sec

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT DISTINCT * FROM `LR_DESTINATION` LR
   LEFT JOIN DESTINATION_MEMORY DM
     ON DM.LR_DESTINATION_ID = LR.ID
   JOIN (SELECT DISTINCT LR_DESTINATION_ID
   	   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
          WHERE ID_GENERIQUE IN (20,21,22,23,24,25,26,27,28,29)) AS RUBJOIN
	 ON RUBJOIN.LR_DESTINATION_ID = LR.ID
  WHERE DONE = 0
    AND URL_SEO <> '/pros/url61234'
	AND ID_DEPARTEMENT = 'D090'
	AND DM.LR_DESTINATION_ID IS NULL
  LIMIT 0,25;

EXISTS : 3sec

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT DISTINCT ID FROM `LR_DESTINATION` LR
   LEFT JOIN DESTINATION_MEMORY DM
     ON DM.LR_DESTINATION_ID = LR.ID
  WHERE EXISTS (SELECT DISTINCT LR_DESTINATION_ID
   	   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
          WHERE ID_GENERIQUE IN (20,21,22,23,24,25,26,27,28,29)
              AND LR_DESTINATION_ID = ID)
    AND DONE = 0
    AND ID_DEPARTEMENT = 'D090'
    AND URL_SEO <> '/pros/url61234'
    AND DM.LR_DESTINATION_ID IS NULL
  LIMIT 0,25;

si je teste la requête de base uniquement avec la jointure et sans distinct : 0.76sec

Code :

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

1
2
3
4
5
6
 
SELECT LR.* FROM `LR_DESTINATION` LR
  JOIN LR_DESTINATION_RUBRIQUE_GENERIQUE  RUBJOIN
    ON RUBJOIN.LR_DESTINATION_ID = LR.ID
 WHERE RUBJOIN.ID_GENERIQUE IN (20,21,22,23,24,25,26,27,28,29)
 LIMIT 0,25;

Code :

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

1
2
3
4
5
6
7
 
+----+-------------+---------+------+--------------------------------------------------+-------+---------+------------------+--------+--------------------------+
| id | select_type | table   | type | possible_keys                                    | key   | key_len | ref              | rows   | Extra                    |
+----+-------------+---------+------+--------------------------------------------------+-------+---------+------------------+--------+--------------------------+
|  1 | SIMPLE      | LR      | ALL  | PRIMARY,IDX_PK_DEP                               | NULL  | NULL    | NULL             | 200435 |                          |
|  1 | SIMPLE      | RUBJOIN | ref  | PRIMARY,idx_lr_destination_id_id_generique,idx_1 | idx_1 | 8       | bo_seo_mca.LR.ID |      1 | Using where; Using index |
+----+-------------+---------+------+--------------------------------------------------+-------+---------+------------------+--------+--------------------------+

C'est tout de même hallucinant ! c'est une requête toute basique avec 100.000 LR_DESTINATION avec une condition de jointure sur 300.000 Rubriques.

Est-ce que cela peut être un problème d'index ?
Est ce que cela peut être un problème de configuration de Mysql ?

[tse_jc]

Bonjour,

permettez-moi quelques remarques

Objectif :
-1 Sélectionner l'ensemble des 'ID', depuis une table 'LR_DESTINATION',

1) A partir de là il me semble normal que votre requête effectue un full scan sur votre table, car c'est ce que vous lui demandez non? Donc il ne faut pas s'étonner du manque de performances.

2) Si vos objectifs traduisent votre requête et la structure de vos tables je doute que vos besoins métiers exprimés correctement correspondraient à ce que vous avez fait, tant au niveau de la structure que de votre requête.

3) Votre SELECT * n'arrange en rien les choses sur vos problèmes de performances, et j'aimerais bien que vous m'expliquiez l'intérêt d'un SELECT DISTINCT * .... O_o

4) Vous n'avez pas crée d'index partiel sur votre colonne URL_SEO défini en VARCHAR 255 qui plus est, qui est contre performant au possible et non pertinent du tout.

5) Vos types de colonnes ne sont pas optimisés pour MySQL je doute de la nécessité d'INT(11) (et donc de la nécessité de gérer des nombres négatifs) ce qui n'arrange en rien l'optimisation du cache de votre plan de requête.

6)

-2 Ces 'ID' sélectionnés ne doivent pas être mémorisé au préalable, donc être absent de la table 'DESTINATION_MEMORY'
-3 Ces 'ID' sélectionnés seront ensuite filtrés sur leur URL et leur DONE.

Je crains fort malheureusement qu'il n'y ait que vous qui puissiez comprendre ce que vous venez d'écrire. Merci d'éclairer notre lanterne sur la signification de cette partie de votre cahier des charges en bon français.

Cordialement,

Jc.

[mickael.camelot]

Bonjour,

Merci d'avoir pris le temps de me répondre,
Effectivement mon exemple avec le DISTINCT * n'était pas pertinent, il s'agit d'une erreur de copie. J'effectue un DISTINCT sur l'ID de la LR_DESTINATION.

Concernant les INDEX j'en ai essayé de multiples mais ils n'étaient pas utilisés dans le plan de requête. Qu'entendez vous par index partiel ? Il s'agit d'un index uniquement sur le champs URL_SEO ?

Le fonctionnel attendu :
Etant complexe, je n'avais spécifié que ce que je pensais nécessaire.
- Sélectionner toutes les LR_DESTINATION référencées dans la table des LR_DESTINATION_RUBRIQUE_GENERIQUE, selon une URL_SEO, un ID_DEPARTEMENT, un DONE.
- Si la requête ne retourne rien ou retourne un nombre de données inférieur à 25, alors Sélectionner toutes les LR_DESTINATION référencées dans la table des LR_DESTINATION_RUBRIQUE_GENERIQUE, selon une URL_SEO, un ID_LOCALITE, un DONE.
- Si la requête ne retourne rien ou retourne un nombre de données inférieur à 25, alors Sélectionner toutes les LR_DESTINATION référencées dans la table des LR_DESTINATION_RUBRIQUE_GENERIQUE, selon une URL_SEO, un ID_REGION, un DONE.

Nous avons un process qui itère selon un certain nombre de critères.
Tant que ces critères ne sont pas satisfaits, nous effectuons cette requête selon différents ID (REGION, DEPARTEMENT, LOCALITE) et selon différentes rubriques. A chaque Itération les paramètres changes.
Enfin, a chaque itération, les destinations déjà sélectionnées doivent être exclu de la seconde requête dans l'optique de ne sélectionner qu'une fois une destination. Afin d'éviter d'ajouter un NOT IN des ID des destinations, nous avons créé une table de type MEMORY dans laquelle nous insérons les destinations déjà sélectionnées lors des précédentes requêtes, d'où le LEFT JOIN sur la table DESTINATION_MEMORY.


J'ai suivi vos remarques et ai modifié tous les type des mes 3 tables afin qu'ils soient plus en adéquations avec leur utilité, sans grand résultat malheureusement.

Stucture BDD

LR_DESTINATION

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE `lr_destination` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `URL_SEO` char(255) NOT NULL,
  `ID_LOCALITE` char(8) NOT NULL,
  `ID_DEPARTEMENT` char(4) NOT NULL,
  `ID_REGION` char(4) NOT NULL,
  `TITRE` char(255) NOT NULL,
  `NOMBRE_LIENS_MIS` smallint(6) unsigned NOT NULL,
  `NOMBRE_LIENS_MAX` smallint(6) unsigned NOT NULL,
  `DONE` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `IDX3` (`URL_SEO`,`ID_DEPARTEMENT`,`DONE`)
) ENGINE=InnoDB AUTO_INCREMENT=199582 DEFAULT CHARSET=latin1

LR_DESTINATION_RUBRIQUE_GENERIQUE

Code :

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

1
2
3
4
5
6
7
8
 
CREATE TABLE `lr_destination_rubrique_generique` (
  `LR_DESTINATION_ID` bigint(20) unsigned NOT NULL,
  `ID_GENERIQUE` smallint(8) unsigned NOT NULL,
  PRIMARY KEY (`ID_GENERIQUE`,`LR_DESTINATION_ID`),
  KEY `IDX_LR_DESTINATION` (`LR_DESTINATION_ID`),
  KEY `IDX_GENERIQUE` (`ID_GENERIQUE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

DESTINATION_MEMORY

Code :

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

1
2
3
4
5
6
7
8
 
CREATE TABLE `destination_memory` (
  `LR_DESTINATION_ID` bigint(20) unsigned NOT NULL,
  `TITRE` char(255) NOT NULL,
  `NOMBRE_LIENS_MIS` smallint(6) unsigned NOT NULL,
  `NOMBRE_LIENS_MAX` smallint(6) unsigned NOT NULL,
  PRIMARY KEY (`LR_DESTINATION_ID`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1

Voici la requête comme j'aurai dû la présenter: durée 0.34s

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT DISTINCT (LR.ID),LR.URL_SEO,LR.TITRE,LR.NOMBRE_LIENS_MIS,LR.NOMBRE_LIENS_MAX   FROM `LR_DESTINATION` LR
   LEFT JOIN DESTINATION_MEMORY DM
     ON DM.LR_DESTINATION_ID = LR.ID
   JOIN (SELECT DISTINCT LR_DESTINATION_ID
   	   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
          WHERE ID_GENERIQUE IN (28,29,22)) AS RUBJOIN
	 ON RUBJOIN.LR_DESTINATION_ID = LR.ID
WHERE DONE = 0
    AND URL_SEO <> '/pros/url199579'
    AND ID_DEPARTEMENT = 'D090'
    AND DM.LR_DESTINATION_ID IS NULL
  LIMIT 0,25;

EXPLAIN

Code :

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

1
2
3
4
5
6
7
8
9
 
+----+-------------+-----------------------------------+--------+-----------------------+---------------+---------+---------------------------+--------+-------------------------------------------+
| id | select_type | table                             | type   | possible_keys         | key           | key_len | ref                       | rows   | Extra                                     |
+----+-------------+-----------------------------------+--------+-----------------------+---------------+---------+---------------------------+--------+-------------------------------------------+
|  1 | PRIMARY     | <derived2>                        | ALL    | NULL                  | NULL          | NULL    | NULL                      |  48865 | Using temporary                           |
|  1 | PRIMARY     | LR                                | eq_ref | PRIMARY,IDX3          | PRIMARY       | 8       | RUBJOIN.LR_DESTINATION_ID |      1 | Using where                               |
|  1 | PRIMARY     | DM                                | eq_ref | PRIMARY               | PRIMARY       | 8       | RUBJOIN.LR_DESTINATION_ID |      1 | Using where; Not exists; Distinct         |
|  2 | DERIVED     | LR_DESTINATION_RUBRIQUE_GENERIQUE | range  | PRIMARY,IDX_GENERIQUE | IDX_GENERIQUE | 2       | NULL                      | 196810 | Using where; Using index; Using temporary |
+----+-------------+-----------------------------------+--------+-----------------------+---------------+---------+---------------------------+--------+-------------------------------------------+

Durée de la requête 0,34s
Cette durée varie selon les paramètres entrés.

Pensez vous qu'il soit possible de faire mieux ?
Il s'agit d'une simple jointure et je ne vois pas quel index serait le plus approprié.

Cordialement.

[tse_jc]

Bonjour,

Vous avez du travail sur la définition de vos domaines de données. Pourquoi c'est important? non pas que cela va vous garantir la résolution de tous vos problèmes de performance mais cela va optimiser le remplissage de vos pages d'index en cache et donc réduire le nombre de pages lues par le plan de requête. Je vous invite à consulter les papiers de SQLpro à ce sujet.

Donc rien que la dessus il y a beaucoup à dire. Travaillez sur un VARCHAR pour l'URLSEO et mettez un index partiel dessus veux dire sur les X premiers caractères significatifs pour votre appli, pour augmenter les performances au lieu de créer un index sur 255 caractères qui ne sera pas pertinent dans tous les cas. Passez le titre également sur un VARCHAR et réduisez au maximum la taille de ce qui est nécessaire à son utilisation.

Ensuite il est évident que vos contraintes métier applicatives n'ont pas été intégrées et prises en compte dans l'architecture de votre BD. Si je devait parier je dirais que votre site tourne sur Zend par rapport à la façon dont c'est fait. Bref là n'est pas la question.

Le problème de la table memory pour gérer vos destinations c'est qu'elle n'est pas temporaire. Du coup elle est globale à l'application et je n'ose imaginer comment se déroule la gestion des accès concurrentiels dans votre système. Cette requête est-elle exécutée en mode connecté ou non connecté? Dans les deux cas comment gérez vous les demandes récursives (par rapport à votre algo de traitement) de deux utilisateurs différents en même temps?

De plus au niveau du stockage de l'URL cela n'est pas optimisé car vous stockez '/pros/url199579' or '/pros/' n'a rien à faire en BD conceptuellement parlant. Vous créez de la volumétrie inutile et en plus cela vous enlève d'autres possibilités de gérer plus efficacement vos conditions WHERE sur URL_SEO. Pensez également à mettre un index de type b-tree si vous travaillez par comparaison et en hash si vous travaillez par égalité dans vos conditions WHERE.

Sinon pour rester toujours constructif autant que faire ce peut, si votre URL est SEO pourquoi ne pas intégrer votre logique SEO dans votre base de donnée au niveau de l'URL? cela vous permettrait de travailler directement sur des valeurs numériques d'index pour vos jointures. Je pense même qu'il y a moyen de gérer votre récursivité de recherche en une seule requête en faisant travailler un index bien construit à votre place (index couvrant en cluster).

Mais si ma mémoire est bonne, je crois que sur les indexs en clusters MySQL est à la rue, ne les gérant pas.

Ensuite l'ordre de vos critères de recherche dans la condition where est déterminante sur vos performances. Si par exemple sur 100 000 lignes vous n'avez qu'une seule ligne ou URL_SEO <> '/pros/url199579' non seulement l'optimiseur MySQL va faire un full scan sur votre table sur ce premier critère car plus rapide que l'utilisation de votre index mais en plus l'utilisation de l'index va créer un overhead de traitement au niveau de l'optimiseur.

Il est tard, je vais m'arrêter là

Cordialement,

Jc.

[mickael.camelot]

Bonjour,

En faite l'url réelle n'est pas celle ci, j'ai mis une url de ce genre en base pour effectuer des tests rapidement, je ne pensais pas que cela pouvait avoir un impact. Cette url est unique. Du coup dans mon cas de test :
URL_SEO
-> varchar(taille à définir)
-> index de type hash à mettre car comparaison utilisant l'opérateur <>

Il ne s'agit pas d'un site mais d'un traitement de batch.
La table memory n'est pas la problématique a ce niveau. En effet celle ci ne contiendra jamais plus de 25 Destinations. D'ailleurs enlever la condition de jointure LEFT JOIN n'affecte pas énormément le plan d'exécution.
Le gros du soucis est la condition de Jointure entre les tables LR_DESTINATION et LR_DESTINATION_RUBRIQUE_GENERIQUE. Si j'enlève toute les conditions de jointure et la clause where pour ne garder que la jointure entre les deux tables j’obtiens quasiment le même temps d'exécution et le même plan.

Donc j'essaie de comprendre ce qui ne va pas avec cette jointure.

Requête

Code :

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

1
2
3
4
5
6
7
8
 
 
SELECT LR.ID,LR.URL_SEO,LR.TITRE,LR.NOMBRE_LIENS_MIS,LR.NOMBRE_LIENS_MAX   FROM `LR_DESTINATION` LR
   JOIN (SELECT DISTINCT LR_DESTINATION_ID
   	   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
          WHERE ID_GENERIQUE IN (28,29,22)) AS RUBJOIN
	 ON RUBJOIN.LR_DESTINATION_ID = LR.ID
  LIMIT 0,25;

EXPLAIN

Code :

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

1
2
3
4
5
6
7
8
 
+----+-------------+-----------------------------------+--------+-----------------------+---------------+---------+---------------------------+--------+-------------------------------------------+
| id | select_type | TABLE                             | type   | possible_keys         | KEY           | key_len | ref                       | rows   | Extra                                     |
+----+-------------+-----------------------------------+--------+-----------------------+---------------+---------+---------------------------+--------+-------------------------------------------+
|  1 | PRIMARY     | <derived2>                        | ALL    | NULL                  | NULL          | NULL    | NULL                      |  48865 | USING TEMPORARY                           |
|  1 | PRIMARY     | LR                                | eq_ref | PRIMARY,IDX3          | PRIMARY       | 8       | RUBJOIN.LR_DESTINATION_ID |      1 | USING WHERE                               |
|  2 | DERIVED     | LR_DESTINATION_RUBRIQUE_GENERIQUE | range  | PRIMARY,IDX_GENERIQUE | IDX_GENERIQUE | 2       | NULL                      | 196810 | USING WHERE; USING INDEX; USING TEMPORARY |
+----+-------------+-----------------------------------+--------+-----------------------+---------------+---------+---------------------------+--------+-------------------------------------------+

Votre dernier point m'interpelle beaucoup plus car en effet c'est ce qui se produit. Si j'effectue ma jointure avec des rubriques dont je suis certain de récupérer beaucoup de données, et bien cela est plus rapide qu'un IN avec une seule rubrique ne retournant qu'un seul enregistrement. Comment y remédier ?

Autre question, lorsque je joue ma requête une première fois, (mysql redémarré, cache vidé, flush sur les table + ANALYZE) celle ci va mettre environs 0.30sec (c'est variable), puis si je rejoue cette requête mais en modifiant les rubriques, l'exécution est super rapide, 0sec à 0,08 sec.
Est ce parce-que le résultat de la requête précédente est en cache et mysql l'utilise, ou est ce la mise en mémoire des index qui est longue la première fois ?

Mon soucis est que je ne dois pas me fier au cache car pour chaque exécution ma requête sera toujours unique. Je n'exécuterai jamais la même requête deux fois.

Merci beaucoup pour vos réponses

[tse_jc]

Bonjour,

Pourriez-vous m'indiquer et me classer parmis tous vos critères de filtrage au niveau la requête, vos critères du plus discriminant au moins discriminant. Est-ce que ce classement reste invariable ou la précédence de ce classement peut-elle changer en fonction du contexte? Si oui ce changement de contexte est-il prévisible?

Cordialement,

Jc.

[mickael.camelot]

Les critères de filtrage présents à chaque requête :
- DONE : indique si une LR_DESTINATION peut être sélectionnée pour la requête. Ce DONE est placé à 1 lorsque le NOMBRE_LIENS_MIS = NOMBRE_LIENS_MAX. Ainsi plus le traitement avance et plus le nombre de LR_DESTINATION disponible (DONE=0) diminue. Le DONE semble un bon discriminant en fin de processus.

- URL_SEO : Discriminant unique, varie à chaque itération. On parcours une liste d'url "source" et l'on sélectionne l'ensemble des LR_DESTINATION qui ont une url différente de la source. C'est toujours du 1 parmi N LR_DESTINATION du coups.

- ID_GENERIQUE : Discriminant de jointure. On sélectionne l'ensemble des LR_DESTINATION ayant l'ensemble des rubriques passées en paramètre. Cet ensemble varie à chaque itération. Il peut y avoir 1 à 30 rubriques dans le IN.

Les critères de filtrage pouvant varier à chaque requête :
-1 ID_LOCALITE : on cherche localement (très nombreuse)
-2 ID_DEPARTEMENT : si on ne trouve pas on recherche au département (centaine de départements)
-3 ID_REGION : si on ne trouve pas on recherche à la région (20 aine de régions)

Ainsi on obtiendra 3 modèles de requêtes :

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 LR.ID,LR.URL_SEO,LR.TITRE,LR.NOMBRE_LIENS_MIS,LR.NOMBRE_LIENS_MAX   
   FROM `LR_DESTINATION` LR
   LEFT JOIN DESTINATION_MEMORY DM
     ON DM.LR_DESTINATION_ID = LR.ID
   JOIN (SELECT DISTINCT LR_DESTINATION_ID
   	   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
          WHERE ID_GENERIQUE IN (X,X,X)) AS RUBJOIN
	 ON RUBJOIN.LR_DESTINATION_ID = LR.ID
WHERE DONE = 0
    AND URL_SEO <> 'XXXXXXXXX'
    AND ID_LOCALITE= 'LXXXXXXXXX'
    AND DM.LR_DESTINATION_ID IS NULL
  LIMIT 0,25;

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 LR.ID,LR.URL_SEO,LR.TITRE,LR.NOMBRE_LIENS_MIS,LR.NOMBRE_LIENS_MAX   
   FROM `LR_DESTINATION` LR
   LEFT JOIN DESTINATION_MEMORY DM
     ON DM.LR_DESTINATION_ID = LR.ID
   JOIN (SELECT DISTINCT LR_DESTINATION_ID
   	   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
          WHERE ID_GENERIQUE IN (X,X,X)) AS RUBJOIN
	 ON RUBJOIN.LR_DESTINATION_ID = LR.ID
WHERE DONE = 0
    AND URL_SEO <> 'XXXXXXXXX'
    AND ID_DEPARTEMENT = 'DXXX'
    AND DM.LR_DESTINATION_ID IS NULL
  LIMIT 0,25;

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 LR.ID,LR.URL_SEO,LR.TITRE,LR.NOMBRE_LIENS_MIS,LR.NOMBRE_LIENS_MAX   
   FROM `LR_DESTINATION` LR
   LEFT JOIN DESTINATION_MEMORY DM
     ON DM.LR_DESTINATION_ID = LR.ID
   JOIN (SELECT DISTINCT LR_DESTINATION_ID
   	   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
          WHERE ID_GENERIQUE IN (X,X,X)) AS RUBJOIN
	 ON RUBJOIN.LR_DESTINATION_ID = LR.ID
WHERE DONE = 0
    AND URL_SEO <> 'XXXXXXXXX'
    AND ID_REGION= 'RXXX'
    AND DM.LR_DESTINATION_ID IS NULL
  LIMIT 0,25

Le changement de contexte n'est pas prévisible, c'est pour cela que nous gérons le filtrage selon différente ID de localité (LOCALITE,DEPARTEMENT,REGION) pour prévoir les cas où aucune information ne serait remontée.

[manzeki]

Autre question, lorsque je joue ma requête une première fois, (mysql redémarré, cache vidé, flush sur les table + ANALYZE) celle ci va mettre environs 0.30sec (c'est variable), puis si je rejoue cette requête mais en modifiant les rubriques, l'exécution est super rapide, 0sec à 0,08 sec.
Est ce parce-que le résultat de la requête précédente est en cache et mysql l'utilise, ou est ce la mise en mémoire des index qui est longue la première fois ?

Oui, Mysql utilise le cache quand la requête est exécutée plus d'une fois. Il faudra ajouter SQL_NO_CACHE après le SELECT. Et bien sur le supprimer en production.

Code :

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

SELECT SQL_NO_CACHE DISTINCT (LR.ID), LR.URL_SEO, LR.TITRE...

[mickael.camelot]

Bonjour,

Merci pour votre réponse,
Mais j'ai l'impression que le SQL_NO_CACHE ne désactive pas vraiment car si je joue deux fois d'affiler ma requête avec cet attribut, la première requête est toujours plus lente que la seconde.

Sinon Je viens tout juste de découvrir une chose, en supprimant tous les index qui n'étaient pas utilisés dans le plan d'exécution de l'optimiseur, mon temps d'exécution de ma requête a été divisé par 2.7 ! J'ai effectué le test plusieurs fois en redémarrant mysql et en vidant le cache pour être certain et effectivement, si je rajoute des index non pertinents et non utilisés par l'optimiseur, le temps d'exécution redécolle !

[tse_jc]

Bonsoir,

On s'est visiblement mal compris quand au caractère discriminant d'un index d'un point de vue SGBDR, mais peut être est-ce moi qui me suis mal exprimé ou mal fait comprendre.

De plus l'absence de normalisation de vos données d'une manière efficiente pose quelques problèmes c'est certain. Bien que cela soit contre productif dans un environnement client serveur de requêter 3 fois le SGBDR pour obtenir un résultat, vu que vous procédez ainsi et l'objet de ce forum étant de vous proposer une solution à votre problème en l'état de votre architecture voici la solution qui devrait vous permettre de réduire vos temps de réponse.


1) Vous n'avez pas besoin du critère URL_SEO dans votre requête car vous recherchez tous les autres. Donc cet index est peu discriminant et donc contre productif. Si vous devez appliquer ce critère il devra donc être appliqué au final sur un jeu de lignes les plus restreintes possibles (l'essentiel du filtrage ayant déjà été effectué donc).

2) Comme il en est de même pour l'ID_GENERIQUE, avant même d'effectuer une quelconque jointure vérifier d'abord l'existence de ligne dans votre BD sur les seuls critères de la localité, du département et de la région.

3) Votre table LR_DESTINATION_RUBRIQUE_GENERIQUE comporte un index inutile.

Si vous avez compris l'essence de cette démarche et cette approche, votre problème devrait être résolu.

Cordialement,

Jc.

[mickael.camelot]

Bonjour,

J'ai essayé de cibler plus le problème.
Il y a vraissemblablement des problèmes de structure au niveau de notre base.
Je vous remercie pour me les avoir ciblé.

Mais si je cible le traitement le plus long il s'agit de la sous requête.

Code :

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

1
2
3
4
 
SELECT DISTINCT LR_DESTINATION_ID
   FROM LR_DESTINATION_RUBRIQUE_GENERIQUE
WHERE ID_GENERIQUE IN (X,X,X)

Cette requête prend 0.20sec à elle seule.
Donc si je dois optimiser quelque chose en premier c'est sur cette table.
Et je vois pas quoi à par un INDEX ?

Merci encore pour votre aide

[tse_jc]

Bonjour,

Oui c'est justement la raison pour laquelle je vous ai dit cela (ID_GENERIQUE n'est pas discriminant) d'où le full scan sur la table source.

EDIT: En gardant votre structure actuelle, appliquer ce que je viens de vous dire revient à restructurer complètement la logique de votre requête.

[tse_jc]

De plus la normalisation de votre base passe nécessairement par la création d'un repository sur vos localités, régions et départements.

Cordialement,

Jc

[aieeeuuuuu]

bonjour,

Cette requête prend 0.20sec à elle seule.
Donc si je dois optimiser quelque chose en premier c'est sur cette table.
Et je vois pas quoi à par un INDEX ?

Dans votre exemple initial, les valeurs de votre IN se suivent.

Si c'est bien le cas, remplacez le IN par un BETWEEN...

[mickael.camelot]

Bonjour,

bonjour,
Dans votre exemple initial, les valeurs de votre IN se suivent.
Si c'est bien le cas, remplacez le IN par un BETWEEN...

Bonne remarque, mais malheureusement, en réalité les valeurs du IN ne se suivent pas.

[mickael.camelot]

De plus la normalisation de votre base passe nécessairement par la création d'un repository sur vos localités, régions et départements.
Cordialement,
Jc

Je vous remercie pour vos conseils.

Je vais tenter de repenser ma base en fonction du volume de données.
Le soucis est que la structure existante était viable pour un ancien fonctionnel qui a évoulé et traite maintenant des millions de données. Nous avons essayé de garder la même structure à quelque chose pres mais cela ne semble pas viable.

Donc dans l'état, avec les données dont je dispose et la stucture de la base je ne pourrai faire mieux.

Encore merci.

[tse_jc]

Bonjour,

Donc dans l'état, avec les données dont je dispose et la stucture de la base je ne pourrai faire mieux.

Ce que j'ai essayé de vous dire, et ce n'est pas faute de vous avoir mis sur la voie, c'est qu'en l'état actuel de votre schéma de données vous pouvez encore améliorer les choses, vous avez de la marge.
Il est clair cependant que pour obtenir des performances optimisées sur MySQL il est nécessaire de revoir en effet votre MCD.

Cordialement,

Jc.

[manzeki]

MySQL peut ne pas utiliser l'index sur le champ DONE, vu que le champs n'a que de deux valeurs distinctes.

J'ai tenté d'explorer deux autres pistes pour reformuler les requêtes.

L'idée est de réduire le scan sur la table LR_DESTINATION_RUBRIQUE_GENERIQUE en incluant le champ LR_DESTINATION_ID qui est absent de la sous requête.

Deux requêtes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT D.ID, D.URL_SEO, D.TITRE, D.NOMBRE_LIENS_MIS, D.NOMBRE_LIENS_MAX
 
FROM LR_DESTINATION D
 
INNER JOIN LR_DESTINATION_MEMORY M ON M.LR_DESTINATION_ID = D.ID
INNER JOIN LR_DESTINATION_RUBRIQUE_GENERIQUE G ON G.ID_GENERIQUE IN (28,29,22) AND G.LR_DESTINATION_ID = D.ID
 
WHERE D.ID_DEPARTEMENT = 'D090' AND D.URL_SEO NOT IN ('/pros/url199579') AND D.DONE
 
LIMIT 0, 25;

ou

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 L.ID, L.URL_SEO, L.TITRE, L.NOMBRE_LIENS_MIS, L.NOMBRE_LIENS_MAX
 
FROM
(
SELECT D.ID, D.URL_SEO, D.TITRE, D.NOMBRE_LIENS_MIS, D.NOMBRE_LIENS_MAX
FROM LR_DESTINATION D
INNER JOIN LR_DESTINATION_MEMORY M ON M.LR_DESTINATION_ID = D.ID
WHERE D.ID_DEPARTEMENT = 'D090' AND D.DONE AND D.URL_SEO NOT IN ('/pros/url199579')
) L
 
INNER JOIN LR_DESTINATION_RUBRIQUE_GENERIQUE G ON G.ID_GENERIQUE IN (25,29,48) AND G.LR_DESTINATION_ID = L.ID
 
LIMIT 0, 25;