{"id":453,"date":"2009-02-18T23:19:25","date_gmt":"2009-02-18T22:19:25","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=453"},"modified":"2011-07-19T15:00:38","modified_gmt":"2011-07-19T14:00:38","slug":"optimisation-de-requetes-comprendre-loptimiseur-de-mysql","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/02\/18\/optimisation-de-requetes-comprendre-loptimiseur-de-mysql\/","title":{"rendered":"Optimisation de requ\u00eates: comprendre l’optimiseur de MySQL"},"content":{"rendered":"


\nLe but de cet article est d’optimiser une simple requ\u00eate\u00a0 (SELECT avg(Population) FROM city GROUP BY CountryCode<\/strong>) et surtout de comprendre comment l’optimiseur proc\u00e8de, en \u00e9tudiant les r\u00e9sultats donn\u00e9s par les variables qui permettent de surveiller le serveur MySQL.<\/p>\n

Le sch\u00e9ma utilis\u00e9 est le sch\u00e9ma world <\/em>t\u00e9l\u00e9chargeable ici<\/a>
\n
\nVoici la structure de la table city<\/em>:<\/p>\n

SHOW CREATE TABLE city\\G
\n*************************** 1. row ***************************
\nTable: city
\nCreate Table: CREATE TABLE city (
\nID int(11) NOT NULL AUTO_INCREMENT,
\nName char(35) NOT NULL DEFAULT \u00a0\u00bb,
\nCountryCode char(3) NOT NULL DEFAULT \u00a0\u00bb,
\nDistrict char(20) NOT NULL DEFAULT \u00a0\u00bb,
\nPopulation int(11) NOT NULL DEFAULT ‘0’,
\nPRIMARY KEY (ID)
\n) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
\n
\n
\n<\/address>\n

La commande EXPLAIN<\/strong>, permet d’avoir le plan d’ex\u00e9cution:<\/p>\n

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\\G
\n*************************** 1. row ***************************
\nid: 1
\nselect_type: SIMPLE
\ntable: c
\ntype: ALL<\/strong><\/span>
\npossible_keys: NULL
\n key: NULL<\/span><\/strong>
\nkey_len: NULL
\nref: NULL
\nrows: 4079
\n Extra: Using temporary; Using filesort<\/strong><\/span>
\n
\n<\/address>\n

L’optimiseur fait un full table scan (type: ALL<\/strong>) ce qui n’est pas forcement une bonne nouvelle. De plus aucun index n’est utilis\u00e9 (key: NULL<\/strong>), ce qui est logique car la table n’en contient pas (sic !)
\nUsing temporary; Using filesort<\/strong> indiquent la cr\u00e9ation d’un table temporaire et le tri des donn\u00e9es (pas tr\u00e8s bon pour les performances surtout si la table temporaire est cr\u00e9\u00e9e sur le disque)
\n
\nLa variable Last_query_cost<\/strong> permet de r\u00e9cup\u00e9rer le co\u00fbt de la requ\u00eate:<\/p>\n

SHOW STATUS LIKE ‘Last_query_cost’\\G<\/address>\n

*************************** 1. row ***************************
\nVariable_name: Last_query_cost
\nValue: 4963.520924
\n
\nAjoutons un index sur la colonne countrycode<\/em>:<\/p>\n

ALTER TABLE city ADD INDEX Idx_cc(CountryCode);<\/address>\n

<\/p>\n

SHOW CREATE TABLE city\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
Table: city<\/address>\n
Create Table: CREATE TABLE city (<\/address>\n
ID int(11) NOT NULL AUTO_INCREMENT,<\/address>\n
Name char(35) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
CountryCode char(3) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
District char(20) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
Population int(11) NOT NULL DEFAULT ‘0’,<\/address>\n
PRIMARY KEY (ID),<\/address>\n
KEY Idx_cc (CountryCode)<\/strong><\/address>\n
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1<\/address>\n

<\/p>\n

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
id: 1<\/address>\n
select_type: SIMPLE<\/address>\n
table: city<\/address>\n
type: ALL<\/address>\n
possible_keys: NULL<\/address>\n
key: NULL<\/span><\/strong><\/address>\n
key_len: NULL<\/address>\n
ref: NULL<\/address>\n
rows: 4079<\/address>\n
Extra: Using temporary; Using filesort<\/address>\n


\n
\nL’index Idx_cc<\/em> ne sert \u00e0 rien<\/span>
\n
\nAjoutons alors un index sur la colonne population <\/em><\/p>\n

ALTER TABLE city ADD INDEX Idx_population(Population);<\/address>\n

<\/p>\n

SHOW CREATE TABLE city\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
Table: city<\/address>\n
Create Table: CREATE TABLE city (<\/address>\n
ID int(11) NOT NULL AUTO_INCREMENT,<\/address>\n
Name char(35) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
CountryCode char(3) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
District char(20) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
Population int(11) NOT NULL DEFAULT ‘0’,<\/address>\n
PRIMARY KEY (ID),<\/address>\n
KEY Idx_cc (CountryCode),<\/address>\n
KEY Idx_population (Population)<\/strong><\/address>\n
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1<\/address>\n
1 row in set (0.00 sec)<\/address>\n

<\/p>\n

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
id: 1<\/address>\n
select_type: SIMPLE<\/address>\n
table: c<\/address>\n
type: ALL<\/address>\n
possible_keys: NULL<\/address>\n
key: NULL<\/span><\/strong><\/address>\n
key_len: NULL<\/address>\n
ref: NULL<\/address>\n
rows: 4079<\/address>\n
Extra: Using temporary; Using filesort<\/address>\n


\nPas mieux ! L’index Idx_population<\/em> ne sert \u00e0 rien.
\n
\nEffacement des 2 index:<\/p>\n

ALTER TABLE city DROP INDEX Idx_cc, DROP INDEX Idx_population;<\/address>\n


\nAjoutons un index composite sur les colonnes population, countrycode<\/em>:<\/p>\n

ALTER TABLE city ADD INDEX Idx_population_cc(Population, CountryCode);<\/address>\n

<\/p>\n

SHOW CREATE TABLE city\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
Table: city<\/address>\n
Create Table: CREATE TABLE `city` (<\/address>\n
`ID` int(11) NOT NULL AUTO_INCREMENT,<\/address>\n
`Name` char(35) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
`CountryCode` char(3) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
`District` char(20) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
`Population` int(11) NOT NULL DEFAULT ‘0’,<\/address>\n
PRIMARY KEY (`ID`),<\/address>\n
KEY `Idx_population_cc` (`Population`,`CountryCode`)<\/strong><\/address>\n
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1<\/address>\n

<\/p>\n

EXPLAIN SELECT avg(Population) FROM city GROUP BY CountryCode\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
id: 1<\/address>\n
select_type: SIMPLE<\/address>\n
table: c<\/address>\n
type: index<\/address>\n
possible_keys: NULL<\/address>\n
key: Idx_population_cc<\/strong><\/span><\/address>\n
key_len: 7<\/address>\n
ref: NULL<\/address>\n
rows: 4079<\/address>\n
Extra: Using index; Using temporary; Using filesort<\/strong><\/span><\/address>\n


\n
\n\u00c7aparait un peu meilleur, l’index Idx_population_cc<\/em> est utilis\u00e9.
\nExtra: using index<\/strong> nous indique que MySQL utilise un index couvrant (covering index), c’est \u00e0 dire que l’information est enti\u00e8rement accessible en parcourant l’index (pas d’acc\u00e8s aux donn\u00e9es).
\nDe\u00a0 plus l’optimiseur fait un full index scan:\u00a0 type: index<\/strong>
\n
\nVoyons le co\u00fbt de la requ\u00eate:<\/p>\n

SHOW STATUS LIKE ‘Last_query_cost’\\G<\/address>\n
*************************** 1. row ***************************
\nVariable_name: Last_query_cost
\nValue: 4963.520924<\/address>\n


\nLa valeur de (l’obscure) Last_query_cost est cependant le m\u00eame que pour les requ\u00eates pr\u00e9c\u00e9dentes…
\n
\nAjoutons un index composite sur les colonnes countrycode,population<\/em><\/p>\n

<\/address>\n
ALTER TABLE test2.city ADD INDEX Idx_cc_population(CountryCode,Population);<\/address>\n

<\/p>\n

SHOW CREATE TABLE city\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
Table: city<\/address>\n
Create Table: CREATE TABLE `city` (<\/address>\n
`ID` int(11) NOT NULL AUTO_INCREMENT,<\/address>\n
`Name` char(35) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
`CountryCode` char(3) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
`District` char(20) NOT NULL DEFAULT \u00a0\u00bb,<\/address>\n
`Population` int(11) NOT NULL DEFAULT ‘0’,<\/address>\n
PRIMARY KEY (`ID`),<\/address>\n
KEY `Idx_population_cc` (`Population`,`CountryCode`),<\/address>\n
KEY `Idx_cc_population` (`CountryCode`,`Population`)<\/strong><\/address>\n
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1<\/address>\n
<\/address>\n

<\/p>\n

EXPLAIN SELECT AVG(Population) FROM city GROUP BY CountryCode\\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
id: 1<\/address>\n
select_type: SIMPLE<\/address>\n
table: c<\/address>\n
type: index<\/address>\n
possible_keys: NULL<\/address>\n
key: Idx_cc_population<\/strong><\/span><\/address>\n
key_len: 7<\/address>\n
ref: NULL<\/address>\n
rows: 4079<\/address>\n
Extra: Using index<\/strong><\/span><\/address>\n
1 row in set (0.00 sec)<\/address>\n
<\/address>\n

<\/p>\n

SHOW STATUS LIKE ‘Last_query_cost’\\G<\/address>\n
*************************** 1. row ***************************
\nVariable_name: Last_query_cost
\nValue: 4963.520924<\/address>\n


\nL’optimiseur estime que le co\u00fbt de la requ\u00eate est toujours le m\u00eame, cependant l’index Idx_cc_population(CountryCode,Population)<\/strong> optimise les performance de notre requ\u00eate<\/span> car il n’y a plus de Using temporary<\/strong> ni de Using filesort<\/strong>.
\nIl suffit de pousser encore un peu plus loin notre analyse pour en \u00eatre d\u00e9finitivement (?) convaincu…
\n
\nInitialisation des variables de sessions<\/p>\n

FLUSH STATUS;<\/address>\n


\nEn utilisant l’index idx_population_cc<\/strong> (le mauvais index)<\/p>\n

SELECT AVG(Population) FROM city use index(idx_population_cc) GROUP BY CountryCode;<\/address>\n
…<\/address>\n
232 rows in set (0.00 sec)<\/strong><\/span><\/address>\n

<\/p>\n

<\/address>\n
SHOW STATUS LIKE ‘handler%’;
\n<\/address>\n
 +----------------------------+-------+<\/pre>\n
 | Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/pre>\n
 +----------------------------+-------+<\/pre>\n
 | Handler_commit\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_delete\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_discover\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_prepare\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_read_first\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 1<\/strong><\/span>\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_read_key\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 4079<\/strong><\/span>\u00a0 |<\/pre>\n
 | Handler_read_next\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 4079<\/strong><\/span>\u00a0 |<\/pre>\n
 | Handler_read_prev\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_read_rnd\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 232<\/strong><\/span>\u00a0\u00a0 |<\/pre>\n
 | Handler_read_rnd_next\u00a0\u00a0\u00a0\u00a0\u00a0 | 233<\/strong><\/span>\u00a0\u00a0 |<\/pre>\n
 | Handler_rollback\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_savepoint\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_savepoint_rollback | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_update\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 3847<\/strong><\/span>\u00a0 |<\/pre>\n
 | Handler_write\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 232<\/strong><\/span>\u00a0\u00a0 |<\/pre>\n
 +----------------------------+-------+<\/pre>\n

\nLes handler sont des indicateurs li\u00e9s au moteur de stockage.
\nHandler_read_first comptabilise le nombre de fois que la premi\u00e8re valeur de l’index est lue.
\nHandler_read_key indique le nombre d’enregistrement r\u00e9cup\u00e9r\u00e9 gra\u00e7e \u00e0 l’index.
\nHandler_read_next indique une lecture ordonn\u00e9e de l’index (une valeur, puis la suivante, puis la suivante…).
\n Handler_read_first, Handler_read_key &\u00a0 Handler_read_next, indiquent l\u00e0, un full index scan<\/span><\/strong>
\n
\nHandler_read_rnd & Handler_read_rnd_next indiquent un full table scan sur la table temporaire<\/span><\/strong>
\n
\nHandler_update nous donne une indication sur le nombre de mise \u00e0 jours dans la table temporaire (\u00e0 cause du tri)
\nHandler_write indique le nombre de lignes ins\u00e9r\u00e9es dans la table temporaire
\n
\nCes 2 derniers param\u00e8tres confirme donc la cr\u00e9ation de la table temporaire et l’op\u00e9ration de tri<\/strong><\/span>
\n
\nPour avoir plus d’informations sur le tri:<\/p>\n
SHOW SESSION STATUS LIKE ‘sort%’;
\n<\/address>\n
 +-------------------+-------+<\/pre>\n
 | Variable_name\u00a0\u00a0\u00a0\u00a0 | Value |<\/pre>\n
 +-------------------+-------+<\/pre>\n
 | Sort_merge_passes | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Sort_range\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Sort_rows\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 232<\/strong><\/span>\u00a0\u00a0 |<\/pre>\n
 | Sort_scan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 1<\/strong><\/span>\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 +-------------------+-------+<\/pre>\n

\nSort_rows: nombre de lignes tri\u00e9es (nombre d’enregistrements de la table temporaire).
\nSort_scan: nombre de tri.
\n<\/p>\n
SHOW SESSION STATUS LIKE ‘created%’;
\n<\/address>\n
 +-------------------------+-------+<\/pre>\n
 | Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/pre>\n
 +-------------------------+-------+<\/pre>\n
 | Created_tmp_disk_tables | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Created_tmp_files\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Created_tmp_tables\u00a0\u00a0\u00a0\u00a0\u00a0 | 1<\/strong><\/span>\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 +-------------------------+-------+<\/pre>\n

\nCreated_tmp_tables: nombre de table temporaire cr\u00e9e. La bonne nouvelle est que la table temporaire est cr\u00e9ee en m\u00e9moire (Created_tmp_disk_tables=0<\/strong>)<\/p>\n

\nr\u00e9initialise les variables de sessions<\/p>\n
FLUSH STATUS;
\n
\n<\/address>\n
En utilisant l’index idx_cc_population<\/strong> (le bon index)<\/p>\n
<\/address>\n
 SELECT AVG(Population) FROM city use index(idx_population_cc) GROUP BY CountryCode;<\/address>\n
 …<\/address>\n
 232 rows in set (0.00 sec)<\/strong><\/span><\/address>\n

\n<\/strong><\/span><\/address>\n
<\/p>\n
 SHOW STATUS LIKE ‘handler%’;<\/address>\n
<\/p>\n
 +----------------------------+-------+<\/pre>\n
 | Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/pre>\n
 +----------------------------+-------+<\/pre>\n
 | Handler_commit\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_delete\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_discover\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_prepare\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_read_first\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 1<\/strong><\/span>\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_read_key\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_read_next\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 4079<\/strong><\/span>\u00a0 |<\/pre>\n
 | Handler_read_prev\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_read_rnd\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_read_rnd_next\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_rollback\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_savepoint\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_savepoint_rollback | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_update\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Handler_write\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 +----------------------------+-------+<\/pre>\n

\nHandler_read_first &\u00a0 Handler_read_next valident le full index scan et puis c’est tout \ud83d\ude42
\n<\/p>\n
SHOW SESSION STATUS LIKE ‘sort%’;
\n<\/address>\n
 +-------------------+-------+<\/pre>\n
 | Variable_name\u00a0\u00a0\u00a0\u00a0 | Value |<\/pre>\n
 +-------------------+-------+<\/pre>\n
 | Sort_merge_passes | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Sort_range\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Sort_rows\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Sort_scan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 +-------------------+-------+<\/pre>\n

\npas de tri
\n<\/p>\n
SHOW SESSION STATUS LIKE ‘created%’;
\n<\/address>\n
 +-------------------------+-------+<\/pre>\n
 | Variable_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value |<\/pre>\n
 +-------------------------+-------+<\/pre>\n
 | Created_tmp_disk_tables | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Created_tmp_files\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 | Created_tmp_tables\u00a0\u00a0\u00a0\u00a0\u00a0 | 0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n
 +-------------------------+-------+<\/pre>\n
pas de table(s) temporaire(s)
\n
\ncqfd
\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Le but de cet article est d’optimiser une simple requ\u00eate  (SELECT avg(Population) FROM city GROUP BY CountryCode) et surtout de comprendre comment l’optimiseur proc\u00e8de, en \u00e9tudiant les r\u00e9sultats donn\u00e9s par les variables qui permettent de surveiller le serveur MySQL.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8,34],"tags":[101,245],"class_list":["post-453","post","type-post","status-publish","format-standard","hentry","category-mysql","category-optimisation","tag-group-by","tag-optimisation"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-7j","jetpack-related-posts":[{"id":237,"url":"https:\/\/dasini.net\/blog\/2008\/11\/28\/les-nouveautes-de-mysql-51-part-15\/","url_meta":{"origin":453,"position":0},"title":"Les nouveaut\u00e9s de MySQL 5.1 — (part 1\/5)","author":"Olivier DASINI","date":"28 novembre 2008","format":false,"excerpt":"Que de chemin parcouru depuis ce 5 juillet 1999, date de lancement de MySQL 3.23.0. En plus des objectifs de simplicit\u00e9 d'administration, de hautes performances et de fiabilit\u00e9, se sont greff\u00e9es, au fur et \u00e0 mesure, les fonctionnalit\u00e9s les plus demand\u00e9es par les utilisateurs. La nouvelle version du SGBDR open-source\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":56,"url":"https:\/\/dasini.net\/blog\/2008\/11\/02\/auto_increment-differences-myisam-innodb\/","url_meta":{"origin":453,"position":1},"title":"AUTO_INCREMENT: Diff\u00e9rences MyISAM – InnoDB","author":"Olivier DASINI","date":"2 novembre 2008","format":false,"excerpt":"La clause, AUTO_INCREMENT, permet \u00e0 MySQL de g\u00e9n\u00e9rer un entier unique pour tout nouvel enregistrement d'une table. Cette clause ne peut se mettre que sur les champs de type entier, index\u00e9 et non nul. Elle est donc souvent utilis\u00e9e comme cl\u00e9 primaire. Cependant, sont comportement n'est pas tout \u00e0 fait\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1163,"url":"https:\/\/dasini.net\/blog\/2012\/01\/09\/ameliorations-de-loptimiseur-dans-mariadb\/","url_meta":{"origin":453,"position":2},"title":"Am\u00e9liorations de l’optimiseur dans MariaDB","author":"Olivier DASINI","date":"9 janvier 2012","format":false,"excerpt":"Les \u00e9quipes de MariaDB ont \u00e9norm\u00e9ment travaill\u00e9es sur l'optimiseur de la version 5.3, notamment en permettant une r\u00e9elle utilisation des sous-requ\u00eates. Voici un effet visuel de ces optimisations: Avec MySQL 5.5, l'utilisation de tables d\u00e9riv\u00e9es (type de sous-requ\u00eates dans la clause FROM d'un SELECT), donne le plan d'ex\u00e9cution suivant:","rel":"","context":"Dans "bench"","block_context":{"text":"bench","link":"https:\/\/dasini.net\/blog\/category\/bench\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1256,"url":"https:\/\/dasini.net\/blog\/2012\/05\/15\/jointure-vs-sous-requete\/","url_meta":{"origin":453,"position":3},"title":"Jointure vs sous-requ\u00eate","author":"Olivier DASINI","date":"15 mai 2012","format":false,"excerpt":"MySQL est connu pour ne pas \u00eatre tr\u00e8s performant avec les sous-requ\u00eates. Ce n'est pas faux, et d'ailleurs c'est encore le cas avec MySQL 5.5. Le contournement consiste en g\u00e9n\u00e9ral \u00e0 r\u00e9\u00e9crire la requ\u00eate, certaines sous-requ\u00eates pouvant \u00eatre ais\u00e9ment r\u00e9\u00e9crite en jointure. C'est le cas de SELECT a FROM T1\u2026","rel":"","context":"Dans "Astuce"","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":430,"url":"https:\/\/dasini.net\/blog\/2009\/01\/29\/influencer-loptimiseur-de-mysql\/","url_meta":{"origin":453,"position":4},"title":"Influencer l’optimiseur de MySQL","author":"Olivier DASINI","date":"29 janvier 2009","format":false,"excerpt":"Il est possible d'influencer l'optimiseur pour qu'il choisisse d'utiliser ou de ne pas utiliser un index particulier. Les clauses \u00e0 placer dans votre requ\u00eate SELECT sont les suivantes: USE INDEX : utilise l'index pass\u00e9 en argument (MySQL ne l'utilisera pas si l'index est plus couteux qu'un full table scan) FORCE\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":403,"url":"https:\/\/dasini.net\/blog\/2009\/01\/13\/mysql-5-les-vues-part-77\/","url_meta":{"origin":453,"position":5},"title":"MySQL 5 : Les vues — (part 7\/7)","author":"Olivier DASINI","date":"13 janvier 2009","format":false,"excerpt":"Conserver la structure d'une table si elle doit \u00eatre modifi\u00e9e La probl\u00e9matique est de mettre \u00e0 jour le sch\u00e9ma de l'application en changeant la structure de certaines tables.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/453","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=453"}],"version-history":[{"count":12,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/453\/revisions"}],"predecessor-version":[{"id":1080,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/453\/revisions\/1080"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}