Discussion :

[jp_rennes]

1) Je ne comprends pas que la création de l'index sur la colonne CountryCode ne soit pas utilisé par l'optimiseur de requête
2) J'ai observé le 'Using temporary'; j'ai passé la variable 'sort_buffer_size' de 1048568 à 3000000 et ça ne change rien. Quelqu'un a une explication ?
mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> show table status like 'City'\G
*************************** 1. row ***************************
Name: City
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 4079
Avg_row_length: 67
Data_length: 273293
Max_data_length: 18858823439613951
Index_length: 43008
Data_free: 0
Auto_increment: 4080
Create_time: 2006-10-20 14:35:43
Update_time: 2006-10-20 14:35:43
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

mysql> show index from City;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| City | 0 | PRIMARY | 1 | ID | A | 4079 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql> explain select CountryCode,avg(Population) from City group by CountryCode;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

mysql> create index codepays on City(CountryCode);
Query OK, 4079 rows affected (0.06 sec)
Records: 4079 Duplicates: 0 Warnings: 0

mysql> explain select CountryCode,avg(Population) from City group by CountryCode;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

[Maximil ian]

Bonjour,

1) L'utilisation d'un index dépend de la sélectivité de celui-ci. Si la colonne country_code contient 50% de valeurs 'fr' et 50% de valeurs 'us' par exemple, un full table scan est plus rentable car l'index n'est pas assez sélectif.

ANALYZE TABLE peut aussi faire que l'index soit mieux pris en compte.

2) L'utilisation de GROUP BY implique très souvent la création d'une table temporaire afin de réaliser les calculs d'agrégats. Cf http://dev.mysql.com/doc/refman/5.0/...imization.html


[ Edit ] en fait c'est plutôt normal qu'aucun index ne soit utilisé, il n'y a pas de clause WHERE dans ta requête et il faut forcément passer en revue toutes les lignes. Le regroupement et les calculs d'agrégats se font ensuite entièrement dans la table temporaire.

[selecta]

Seule solution, passer par un index couvrant 2 parties
part1 = CountryCode
part2 = Population

l'OPTIMISEUR de MYSQL sur ce type de requête n'a aucun interet à passer par l'index CountryCode seul parce que dans tous les cas il devra aller chercher l'info complémentaire (colonne Population) dans les datas.
Au final ça entrainera une consommation en E/S plus importante que de faire un scan de table.

Cordialement

Selecta

[Maximil ian]

Seule solution, passer par un index couvrant 2 parties
part1 = CountryCode
part2 = Population

Je ne vois pas en quoi un tel index peut être utilisé ?

Le but du jeu est de faire la moyenne de la population. Ca nécessite simplement d'additionner toutes les populations et de diviser par leur nombre... Donc aucune recherche sur une population donnée, qui pourrait être accélérée par cet index.

[Sivrît]

L'utilité de cet index, à la limite et si on est vraiment désespéré, est que toutes les données utilisées dans la requête (et rien qu'elles, et peut-être groupé comme il le faut) sont dans l'index donc pas besoin de lire la table... en théorie. Vu qu'il n'y a pas de where je ne sais pas si mysql va percuter. Et le gain est discutable même si ça marche.

[selecta]

L'optimiseur de MYSQL percutera sur une telle requête avec l'index couvrant bi-parties.
Ensuite tout est question de compromis.
Si la table a 10.000 lignes, le gain sera pas extraordinaire.
si la table a 100 millions de lignes, ça commence à valoir le cout.

Il y a pas mal de chose sur le WEB a propos des index couvrants qui ont pour l'avoir pratiqué pas mal d'incidence sur les optimiseurs de Sybase, Oracle, SQL SERVER et bien sur MySQL

Pour le seuil de 50%, je l'abaisserai plutôt vers les 20 à 30 % pour de nombreux SGBD, y compris MYSQL.

[Maximil ian]

Autant pour moi, je n'avais pas pensé à cette optimisation...

Pour le seuil de 50%, je l'abaisserai plutôt vers les 20 à 30 % pour de nombreux SGBD, y compris MYSQL.

Oui, 50 % c'était juste un exemple