Discussion :

[Nympheasi]

Bonjour,

J'aimerais optimiser une requête sur une base de données MySQL que voici:

Code :

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

1
2
3
4
5
6
 
SELECT adresses.ville, COUNT( mac ) AS CGR_VoIP FROM adresses 
LEFT JOIN community ON community.ville = adresses.ville 
WHERE community.region IN ('mauricie') 
AND adresses.mac IN (SELECT adresses.mac FROM adresses, BTS.MGW WHERE adresses.mac = BTS.MGW.mac_modem) 
GROUP BY community.ville ORDER BY CGR_VoIP

Pour vous faire un peu le portrait de cette requête, elle couvre 3 tables réparties dans 2 BD:

BD#1 ==> BTS contient la table MGW
BD#2 ==> Rapports contient les tables adresses et community

Présentement cette requête prend 1.3 secondes et j'aimerais avoir plus court. Donc j'ai essayer de mettre des index sur tout les champs compris dans cette requête. Ça a amélioré un peu le temps de recherche mais de très peu.

Est-ce qu'au premier coup d'oeil quelqu'un aurait une piste à me donner pour optimiser cette requête? Ou est-ce que cela vous prend plus d'info?

Merci d'avance pour votre aide.

[kedare]

Il nous manque quelques information qui seraient utiles :
- Que renvoie EXPLAIN sur ta requete ? (EXPLAIN TaRequete)
- Quel est la structure de ta table ? (SHOW CREATE TABLE NomDeTaTable)

[CinePhil]

Pourquoi ne pas faire directement une seconde jointure plutôt qu'une sous-requête IN ?

Code :

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

1
2
3
4
5
6
7
SELECT a.ville, COUNT( a.mac ) AS CGR_VoIP 
FROM rapports.adresses AS a
LEFT JOIN rapports.community AS c ON c.ville = a.ville 
INNER JOIN BTS.MGW AS m ON a.mac = m.mac_modem
WHERE community.region IN ('mauricie') 
GROUP BY c.ville 
ORDER BY CGR_VoIP

[Nympheasi]

Boinjour,

J'ai obtenu une bonne partie de l'aide recherchée sur le chat. Ma requête devient maintenant:

Code :

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

1
2
3
4
5
6
 
SELECT adresses.ville, COUNT( mac ) AS CGR_VoIP FROM adresses 
INNER JOIN community ON community.ville = adresses.ville 
INNER JOIN BTS.MGW ON adresses.mac = BTS.MGW.mac_modem
WHERE community.region = 'mauricie' 
GROUP BY community.ville ORDER BY CGR_VoIP

Elle passe de 1.3 sec de temps de réponse à 0.8 sec. Par contre il me reste encore du boulot pour reduire encore le temps de réponse.

@kedare, voici les infos demandées:

Code :

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

1
2
3
4
5
 
id  select_type  table      type  possible_keys                                 key                  key_len  ref                      rows  Extra  
1   SIMPLE       community  ref   index_ville,index_region,index_ville_region   index_ville_region   22       const                    31    Using where; Using index; Using temporary; Using filesort
1   SIMPLE       adresses   ref   PRIMARY,index_ville,index_mac_ville           index_ville          103      rapports.community.ville 606   Using where 
1   SIMPLE       MGW        ref   mac                                           mac                  22       rapports.adresses.mac    1     Using index

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
 
Table    Create Table  
adresses CREATE TABLE `adresses` (
         `mac` varchar(20) NOT NULL,
         `ubr` varchar(10) NOT NULL,
         `no_civique` varchar(20) DEFAULT NULL,
         `no_civique2` varchar(20) DEFAULT NULL,
         `rue` varchar(100) DEFAULT NULL,
         `app` varchar(10) DEFAULT NULL,
         `ville` varchar(100) DEFAULT NULL,
         `date_installation` date DEFAULT NULL,
         PRIMARY KEY (`mac`),
         KEY `index_ville` (`ville`),
         KEY `index_mac_ville` (`mac`,`ville`)
         ) ENGINE=MyISAM DEFAULT CHARSET=latin1

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
 
Table     Create Table  
community CREATE TABLE `community` (
          `id` int(10) NOT NULL AUTO_INCREMENT,
          `date_lancement` date NOT NULL,
          `ville` varchar(100) NOT NULL,
          `dial_plan` varchar(50) NOT NULL,
          `region` varchar(20) NOT NULL,
          `commentaires` varchar(500) NOT NULL,
          PRIMARY KEY (`id`),
          KEY `index_dial_plan` (`dial_plan`),
          KEY `index_ville` (`ville`),
          KEY `index_region` (`region`),
          KEY `index_ville_region` (`region`,`ville`)
          ) ENGINE=MyISAM AUTO_INCREMENT=162 DEFAULT CHARSET=latin1

Code :

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

1
2
3
4
5
6
7
8
9
10
 
Table  Create Table  
MGW    CREATE TABLE `mgw` (
      `mgw_id` varchar(12) NOT NULL,
      `tsap_addr` varchar(40) NOT NULL,
      `fqdn_mc` varchar(45) NOT NULL,
      `mac_modem` varchar(20) NOT NULL,
       KEY `Flap` (`mgw_id`),
       KEY `mac` (`mac_modem`)
       ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Donc voilà, je tiens à préciser que je débute en ce qui a trait a l'optimisation de mes tables donc c'est fort possible qu'il y aille des points qui font non-sens, svp m'en aviser.

Merci

[jeca]

Bonjour,

Une table "ville" serait certainement bienvenue. Elle permettrait d'éviter les redondances, et de remplacer les colonnes "ville" des 2 tables par la clé de celle-ci. La jointure de ta requête serait certainement plus rapide.

[Nympheasi]

@ Jeca: Aurais-tu un exemple car je ne suis pas certain de bien comprendre?

[CinePhil]

Actuellement, tu as une colonne ville en VARCHAR(100) dans deux tables.
La normalisation de la base de données voudrait que tu aies une table des villes et que ces deux tables ne contiennent que l'identifiant de la ville de type entier.
Ca pourrait accélérer la recherche dans les index et surtout ça éviterait les erreurs ou différences de saisie (Saint-Ouen, St-Ouen, Saint Ouen, St Ouen) entre les tables.

[Nympheasi]

Ok, je comprend, le hic c'est que l'une de ces tables, ce n'est pas moi qui la gère, je n'ai que les droits de SELECT (tables adresses) donc ça complique un peu la tâche... mais je vais voir ce que je peux faire.

Merci

[Nympheasi]

Il y a aussi cette requête que je me demande comment me passer de la sous-requête au profit d'un jointure qui serait peut-être plus rapide:

Code :

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

1
2
3
4
5
 
SELECT COUNT( mac ) AS CGR_VoIP FROM adresses 
INNER JOIN community ON community.ville = adresses.ville 
WHERE community.ville = 'YAMASKA' 
AND adresses.mac NOT IN (SELECT adresses.mac FROM adresses, BTS.MGW WHERE adresses.mac = BTS.MGW.mac_modem)

Voici le Explain:

Code :

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

1
2
3
4
5
6
 
id  select_type        table     type   possible_keys                     key                key_len  ref   rows  Extra  
1   PRIMARY            adresses  ref    index_ville                       index_ville        103      const 1     Using where 
1   PRIMARY            community index  NULL                              index_ville_region 124      NULL  161   Using where; Using index; Using join buffer 
2   DEPENDENT SUBQUERY MGW       ref    mac                               mac                22       func  1     Using index 
2   DEPENDENT SUBQUERY adresses  eq_ref PRIMARY,index_mac_ville,index_mac PRIMARY            22       func  1     Using where; Using index

Pour les tables, ce sont les même qu'énumérées plus haut.

[ced]

Bonjour,

Le NOT IN (tout comme le IN, d'ailleurs) peut souvent être remplacé par une jointure externe. Ca pourrait donner quelque chose comme ça :

Code :

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

1
2
3
4
5
6
SELECT COUNT( mac ) AS CGR_VoIP 
FROM adresses 
INNER JOIN community ON community.ville = adresses.ville
LEFT JOIN  BTS.MGW on adresses.mac = BTS.MGW.mac_modem
WHERE community.ville = 'YAMASKA' 
AND BTS.MGW.mac_modem IS NULL

Il nous faut plus d'information sur la structure des tes tables et le sens de chaque champ pour pouvoir t'aider sans faire d'erreur...

ced

[Nympheasi]

Merci Ced, ton exemple m'a permis de finaliser ma requête de façon plus optimale!