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.