[Optimisation] index non utilisé et using temporary
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)
