Bonjour à tous,
Je cherche à améliorer une requête qui aujourd'hui prend entre 9 et 11 secondes à s'exécuter !
Contexte global (Application sous PHP + Mysql version 5.0.51a-log)
J'ai une requête sert à afficher un tableau des demandes d'informations qui parviennent de sites internet. Aujourd'hui, dans certains cas, le temps d'exécution est tout simplement inacceptable.
Pour comprendre la requête telle qu'elle existe aujourd'hui, il me semble bon de décrire rapidement les relations entre tables et les principaux attributs. Vous trouvez à la fin la définition des tables.
Un contact fait une ou plusieurs demandes. Chaque contact est affecté ensuite à un et un seul commercial.
- Un commercial appartient à un est un seul territoire (notion de zone commerciale). Un commercial peut voir toutes les demandes des contacts gérés par les autres commerciaux appartenant au même territoire que lui.
- Un contact a notamment 2 attributs email1 et email2.
La requête doit afficher le tableau suivant :
-date de la demande
- site web d'où vient la demande
- nom du contact (qui a fait la demande)
- adresse email du contact (email1)
- nombre total de demandes fait par ce contact - l'email 1 est utilisé comme critère de sélection. On doit donc afficher le nombre de demandes faites par un même contact eu regard à son email 1 . Idéalement, je souhaiterais que ce nombre de demandes soit fait à partir de l'adresse email1 et email2.
- plus un certain nombre d'infos comme : pays du contact, nom du commercial qui le gère, ....
Pour tenir compte du fait que chaque utilisateur ne peut voir que les contacts et demandes appartenant à son territoire je procède ainsi :
Lorsque l'utilisateur se connecte à l'application, je crée une view de la table contact.
3 Cas peuvent se produire.
// Détermination des contacts visibles par l'utilisateur en fonction de son territoire
1) l'utilisateur peut tout voir
2) l'utilisateur ne peut avoir accès qu'aux contacts qu'il gère !
Code : Sélectionner tout - Visualiser dans une fenêtre à part CREATE OR REPLACE VIEW v_contact_".$_SESSION['user_id']." AS SELECT contact.* FROM `contact`";
3) L'utilisateur a accès aux contacts de son territoire
Code : Sélectionner tout - Visualiser dans une fenêtre à part CREATE OR REPLACE VIEW v_contact_".$_SESSION['user_id']." AS SELECT contact.* FROM `contact` WHERE commercial_id='".$_SESSION['user_id']."'";
Utilisant une variable de session $_SESSION['user_id'], à tout moment dans le code on peut afficher des résultats en utilisant v_contact_".$_SESSION['user_id'].". Donc par exemple v_contact_AZE correspond aux contacts auxquels le commercial AZE peut accéder. On se retrouve donc avec autant de vues v_contact_quelquechose qu'il y a d'utilisateur.
Code : Sélectionner tout - Visualiser dans une fenêtre à part CREATE OR REPLACE VIEW v_contact_".$_SESSION['user_id']." AS SELECT contact.* FROM `contact` LEFT JOIN commercial ON contact.commercial_id = commercial.id_commercial WHERE commercial.territoire_id=(SELECT territoire_id FROM commercial where commercial.id_commercial='".$_SESSION['user_id']."')";
Ensuite, en l'absence de critère de recherche spécifique, j'utilise la requête suivante : (ici l'utilisateur est AZE)
Quelque soit la requête (utilisation de critères de recherche ou non), on affiche au plus 20 lignes par page. Ceci explique la présence du LIMIT 0 , 20.
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 d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status,d.source_lien,source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI,COUNT(*) AS nb FROM v_contact_AZE c INNER JOIN ( SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI,site_source, status,source_lien,type_prestation FROM demande d3 INNER JOIN v_contact_AZE c ON d3.contact_id = c.id_contact ORDER BY d3.id_demande DESC LIMIT 0,20) AS d ON d.contact_id = c.id_contact INNER JOIN contact c2 ON c.email1 = c2.email1 INNER JOIN demande d2 ON d2.contact_id = c2.id_contact LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial LEFT JOIN pays ON c.pays = pays.id_pays LEFT JOIN site_wi ON d.site_source=site_wi.site_wi LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement GROUP BY c.email1, d.id_demande ORDER BY id_demande DESC
Grosso modo, en l'absence de critère particulier de recherche, l'affichage des enregistrements est tout à fait acceptable : (20 total, traitement: 0.0068 sec.)
Un explain de la requête donne :
Maintenant si on utilise des critères de recherche, exemple toutes les demandes dont le d.type_prestation!='BKE' :
On atteint facilement les 10 secondes ... Pour avoir les 20 enregistrements, j'ai mis le DESC LIMIT 0,20 en fin de requête.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10 SELECT d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status,d.source_lien,source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.tag_suivi_direction,d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI,COUNT(*) AS nb FROM v_contact_AZE c INNER JOIN ( SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI,tag_suivi_direction, site_source, status,source_lien,type_prestation FROM demande d3 ) AS d ON d.contact_id = c.id_contact INNER JOIN contact c2 ON c.email1 = c2.email1 INNER JOIN demande d2 ON d2.contact_id = c2.id_contact LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial LEFT JOIN pays ON c.pays = pays.id_pays LEFT JOIN site_wi ON d.site_source=site_wi.site_wi LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement WHERE 1 AND d.type_prestation!='BKE' GROUP BY c.email1, d.id_demande ORDER BY id_demande DESC LIMIT 0,20
Si on fait un explain, voici le résultat :
Bref, c'est le bordel !
Comment faire pour optimiser cette requête ? Le fait d'utiliser une VIEW est-il contreproductif ? Si oui, comment faire autrement ? Changer le MLD ?
Comment intégrer également l'email2 comme critère pour compter le nombre de demandes faites par un contact ?
Afin de faciliter le travail de ceux qui auront le courage et la gentillesse de bien vouloir m'aider, voici la définition simplifiée des table mise en oeuvre.
Par avance, merci
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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118 CREATE TABLE `demande` ( `id_demande` int(11) NOT NULL auto_increment, `site_source` enum('Admin' ,'crm.tel') character set latin1 NOT NULL default 'Admin', `date_demande` datetime NOT NULL default '0000-00-00 00:00:00', `type_prestation` enum('NC','B','BS','BSH','LUX','BKE') character set latin1 NOT NULL default 'NC', `contact_id` int(11) NOT NULL default '0', `num_dossier` varchar(7) collate utf8_unicode_ci NOT NULL default '00.0000', `status` enum('Pending','Signed','Lost') character set latin1 NOT NULL default 'Pending', PRIMARY KEY (`id_demande`), KEY `contact_id` (`contact_id`), KEY `type_prestation` (`type_prestation`) ) ENGINE=MyISAM AUTO_INCREMENT=52262 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=52262 ; -- -- Contenu de la table `demande` -- INSERT INTO `demande` (`id_demande`, `site_source`, `date_demande`, `type_prestation`, `contact_id`, `num_dossier`, `status`) VALUES (52180, 'crm.tel', '2013-02-19 19:11:09', 'B', 61476, '0', 'Lost'), (52181, 'crm.tel', '2013-02-22 09:39:23', 'B', 61478, '22.0003', 'Pending'), (52182, 'crm.tel', '2013-02-22 10:40:41', 'B', 61478, '22.0002', 'Signed'), (52183, 'crm.tel', '2013-03-01 12:34:00', 'B', 61479, '22.0001', 'Pending'), (52185, 'crm.tel', '2013-03-07 09:40:44', 'B', 61480, '0', 'Pending'), (52186, 'crm.tel', '2013-03-07 09:41:58', 'B', 61481, '0', 'Pending'), (52187, 'crm.tel', '2013-03-07 09:43:02', 'B', 61480, '0', 'Pending'), (52237, 'Admin', '2013-03-28 12:02:29', 'BKE', 61504, '00.0000', 'Pending'), (52239, 'Admin', '2013-03-31 11:39:12', 'BKE', 61506, '00.0000', 'Pending'), (52243, 'Admin', '2013-04-04 23:57:18', 'BKE', 61510, '00.0000', 'Pending'), (52244, 'Admin', '2013-04-08 19:11:13', 'BKE', 61511, '00.0000', 'Pending'), (52257, 'Admin', '2013-04-09 17:52:42', 'BKE', 61524, '03.2589', 'Pending'), (52260, 'Admin', '2013-04-09 18:02:05', 'BKE', 61527, '00.0000', 'Pending'); -- -- Structure de la table `contact` -- CREATE TABLE `contact` ( `id_contact` int(11) NOT NULL auto_increment, `prenom` varchar(100) collate utf8_unicode_ci NOT NULL, `nom` varchar(100) collate utf8_unicode_ci NOT NULL, `pays` char(2) character set latin1 NOT NULL default '99', `email1` varchar(250) collate utf8_unicode_ci NOT NULL, `email2` varchar(250) collate utf8_unicode_ci NOT NULL, `commercial_id` char(3) character set latin1 NOT NULL default 'XXX', `cycle` enum('Leads','Client') character set latin1 NOT NULL default 'Leads', PRIMARY KEY (`id_contact`), KEY `email1` (`email1`), KEY `contact_prioritaire` (`contact_prioritaire`), KEY `email2` (`email2`) ) ENGINE=MyISAM AUTO_INCREMENT=61529 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=61529 ; -- -- Contenu de la table `contact` -- INSERT INTO `contact` (`id_contact`, `prenom`, `nom`, `pays`, `email1`, `email2`, `commercial_id`, `cycle`) VALUES (61476, 'Léon', 'Huns', 'PL', 'testea@nomail.fr', '', 'AZE', 'Client'), (61478, 'Jean', 'Goute', 'CH', 'demandein@nomail.fr', 'abce@nomail.fr', 'AZE', 'Client'), (61479, '', 'Dujardin', 'FR', 'demande@nomail.fr', 'doublon@free.fr', 'CAR', 'Client'), (61480, 'Marcus', 'Yourshi', 'US', 'marcus@nomail.at', '', 'AZE', 'Leads'), (61481, 'Diana', 'Doublon', 'US', 'doublon@nomail.com', '', 'CNA', 'Leads'), (61504, 'Marc', 'Morandini', 'FR', 'moran@nomail.fr', '', 'AZE', 'Leads'), (61506, 'Diana', 'Doublon', 'FR', 'piscali@nomail.fr', 'doublon@nomail.com', 'XXX', 'Leads'), (61510, 'Mylène', 'Far', 'FR', 'far@nomail.fr', '', 'XXX', 'Leads'), (61511, 'Eric', 'Avinant', 'AR', 'davidaviant@nomail.fr', '', 'XXX', 'Leads'), (61524, 'Claude', 'Duras', 'FR', 'claude@nomail.at', '', 'XXX', 'Leads'), (61527, 'Henri', 'Haas', 'FR', 'henri.haas@nomail.com', '', 'XXX', 'Leads'); -- -- Structure de la table `pays` -- CREATE TABLE `pays` ( `id_pays` char(2) character set latin1 NOT NULL default '99', `pays_lib_anglais` varchar(150) character set latin1 NOT NULL default '', PRIMARY KEY (`id_pays`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Contenu de la table `pays` -- INSERT INTO `pays` (`id_pays`, `pays_lib_anglais`) VALUES ('AR', 'Argentina'), ('CH', 'Switzerland'), ('FR', 'France'), ('PL', 'Poland'), ('US', 'United States'); CREATE TABLE `statut_abonnement` ( `id_statut_abonnement` tinyint(4) NOT NULL auto_increment, `statut_ab_court_anglais` char(1) NOT NULL default '', PRIMARY KEY (`id_statut_abonnement`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Contenu de la table `statut_abonnement` -- INSERT INTO `statut_abonnement` (`id_statut_abonnement`, `statut_ab_court_anglais`) VALUES (1, 'N'), (2, 'P'), (3, 'Y'), (4, 'U'); -- -- Structure de la table `site_wi` -- CREATE TABLE `site_wi` ( `id_site_wi` tinyint(4) NOT NULL default '0', `site_wi` varchar(50) NOT NULL default '', `abreviation_site` char(3) NOT NULL default '', PRIMARY KEY (`site_wi`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Contenu de la table `site_wi` -- INSERT INTO `site_wi` (`id_site_wi`, `site_wi`, `abreviation_site`) VALUES (1, 'Admin', 'CRM'), (15, 'crm.tel', 'TEL');
Tavar
Partager