CODEWITHSUNDEEP
mysqlsql
Hayi
Hayi

Reputation: 6236

Wrong index chosen by mysql

  1. why i got Using where; Using index; Using temporary; Using filesort in my explain cmd because it slow down my query 1.3s .

explain :

+----+-------------+--------------+--------+---------------------------------------------------+---------------------+---------+-----------------------------+-------+-----------------------------------------------------------+
| id | select_type | table        | type   | possible_keys                                     | key                 | key_len | ref                         | rows  | Extra                                                     |
+----+-------------+--------------+--------+---------------------------------------------------+---------------------+---------+-----------------------------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | lieuexecut1_ | ref    | fk_ao_lieuex,fk_region_lieuex,idao_idregion_index | idao_idregion_index | 5       | const                       | 27343 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | appeloffre0_ | eq_ref | PRIMARY                                           | PRIMARY             | 4       | ao.lieuexecut1_.appel_offre |     1 |                                                           |
+----+-------------+--------------+--------+---------------------------------------------------+---------------------+---------+-----------------------------+-------+-----------------------------------------------------------+
  1. Why when i force the use of index fk_ao_lieuex of my foreing key use index ( fk_ao_lieuex) the query run fast 0.0x s .

explain

+----+-------------+--------------+-------+---------------+--------------+---------+--------------------------------+------+-------------+
| id | select_type | table        | type  | possible_keys | key          | key_len | ref                            | rows | Extra       |
+----+-------------+--------------+-------+---------------+--------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | appeloffre0_ | index | PRIMARY       | PRIMARY      | 4       | NULL                           |   10 |             |
|  1 | SIMPLE      | lieuexecut1_ | ref   | fk_ao_lieuex  | fk_ao_lieuex | 4       | ao.appeloffre0_.ID_APPEL_OFFRE |    1 | Using where |
+----+-------------+--------------+-------+---------------+--------------+---------+--------------------------------+------+-------------+

here is my query :

select  .... from ao.appel_offre appeloffre0_ 
inner join ao.lieu_execution lieuexecut1_ 
on appeloffre0_.ID_APPEL_OFFRE=lieuexecut1_.appel_offre 
where lieuexecut1_.region=1
group by appeloffre0_.ID_APPEL_OFFRE 
order by appeloffre0_.ID_APPEL_OFFRE desc 
limit 10

my indexes of table lieu_execution

+----------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-
| Table          | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+----------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-
| lieu_execution |          0 | PRIMARY             |            1 | id          | A         |       62127 |     NULL | NULL   |      | BTREE      |
| lieu_execution |          1 | fk_ao_lieuex        |            1 | appel_offre | A         |       62127 |     NULL | NULL   |      | BTREE      |
| lieu_execution |          1 | fk_province_lieuex  |            1 | province    | A         |           2 |     NULL | NULL   | YES  | BTREE      |
| lieu_execution |          1 | fk_region_lieuex    |            1 | region      | A         |           2 |     NULL | NULL   | YES  | BTREE      |
| lieu_execution |          1 | fk_ville_lieuex     |            1 | ville       | A         |          13 |     NULL | NULL   | YES  | BTREE      |
| lieu_execution |          1 | idao_idregion_index |            1 | region      | A         |         227 |     NULL | NULL   | YES  | BTREE      |
| lieu_execution |          1 | idao_idregion_index |            2 | appel_offre | A         |         227 |     NULL | NULL   |      | BTREE      |
+----------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-

Upvotes: 1

Views: 146

Answers (1)

zerkms
zerkms

Reputation: 254886

You need to do 2 things:

  1. Create a composite (region, appel_offre) index
  2. You change your GROUP BY and ORDER BY clauses to use lieuexecut1_.appel_offre column instead.

It improves the performance because now mysql optimizer has a chance to use the just created index for both using it in lieuexecut1_.region=1 predicate and further GROUP BY and sorting. Otherwise it must do everything before applying LIMIT hence it's slow.

Upvotes: 2

Related Questions