| Bug #88786 | Optimzer choose wrong index | ||
|---|---|---|---|
| Submitted: | 6 Dec 2017 13:31 | Modified: | 13 Apr 2018 3:59 |
| Reporter: | Olivier ORLANDO | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | alias, Optimizer, wrong index | ||
[6 Dec 2017 13:33]
Olivier ORLANDO
Contain 2 tables in order to play queries
Attachment: Dump20171206-1.zip (application/zip, text), 2.62 MiB.
[8 Dec 2017 14:01]
MySQL Verification Team
Hi! I have tested your test case and you are indeed right. The plan with FORCE INDEX is much better. Without FORCE INDEX: +----+-------------+-------------+------------+------+--------------------------------------+------------------------------+---------+--------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+--------------------------------------+------------------------------+---------+--------------------+------+----------+----------------------------------------------+ | 1 | SIMPLE | container2_ | NULL | ref | PRIMARY,FK_87sftj2k6a1bucnj5ab2rs4s3 | FK_87sftj2k6a1bucnj5ab2rs4s3 | 9 | const | 2198 | 100.00 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | container1_ | NULL | ref | PRIMARY,FK_87sftj2k6a1bucnj5ab2rs4s3 | FK_87sftj2k6a1bucnj5ab2rs4s3 | 9 | bug.container2_.id | 5 | 100.00 | NULL | | 1 | SIMPLE | content0_ | NULL | ref | FK_gmhpacw2qahxt0gvpvc7243sc | FK_gmhpacw2qahxt0gvpvc7243sc | 9 | bug.container1_.id | 1 | 100.00 | Using index | +----+-------------+-------------+------------+------+--------------------------------------+------------------------------+---------+--------------------+------+----------+----------------------------------------------+ With FORCE INDEX: +----+-------------+-------------+------------+--------+--------------------------------------+------------------------------+---------+---------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+--------+--------------------------------------+------------------------------+---------+---------------------------+------+----------+-------------+ | 1 | SIMPLE | container1_ | NULL | index | NULL | IDX_CONTAINER_CREATIONDATE | 6 | NULL | 119 | 100.00 | Using where | | 1 | SIMPLE | container2_ | NULL | eq_ref | PRIMARY,FK_87sftj2k6a1bucnj5ab2rs4s3 | PRIMARY | 8 | bug.container1_.parent_id | 1 | 5.00 | Using where | | 1 | SIMPLE | content0_ | NULL | ref | FK_gmhpacw2qahxt0gvpvc7243sc | FK_gmhpacw2qahxt0gvpvc7243sc | 9 | bug.container1_.id | 1 | 100.00 | Using index | +----+-------------+-------------+------------+--------+--------------------------------------+------------------------------+---------+---------------------------+------+----------+-------------+ Verified as reported.
[13 Apr 2018 3:59]
Erlend Dahl
Duplicate of Bug#83323 Optimizer chooses wrong plan when joining 2 tables
Description: Optimze choice wrong index with a query contains 2 ffirent aliases inner join on same table. As you can see, on the "How to repeat part", without "force index", the Optimizer choose automaticly the same index for the 2 alias (cointainer1_ and container2_) instead of choosing the good index for each one : container1_ : IDX_CONTAINER_CREATIONDATE because of "ORDER BY" container2_ : Foreign Key : becauson inner join How to repeat: play this query (with the attached file) ------------------------------------------ select content0_.id FROM social_content content0_ INNER JOIN social_container container1_ ON content0_.container = container1_.id INNER JOIN social_container container2_ ON container1_.parent_id = container2_.id WHERE container2_.parent_id = 215 ORDER BY container1_.creation_date DESC LIMIT 6; ------------------------------------------ Explain of the Query 1 SIMPLE container2_ ref PRIMARY,FK_87sftj2k6a1bucnj5ab2rs4s3 FK_87sftj2k6a1bucnj5ab2rs4s3 9 const 2198 100.00 Using index; Using temporary; Using filesort 1 SIMPLE container1_ ref PRIMARY,FK_87sftj2k6a1bucnj5ab2rs4s3 FK_87sftj2k6a1bucnj5ab2rs4s3 9 tstMysql.container2_.id 8 100.00 1 SIMPLE content0_ ref FK_gmhpacw2qahxt0gvpvc7243sc FK_gmhpacw2qahxt0gvpvc7243sc 9 tstMysql.container1_.id 1 100.00 Using index ------------------------------------------ if you add a force index (the good index) on container1_: select content0_.id FROM social_content content0_ INNER JOIN social_container container1_ force index (IDX_CONTAINER_CREATIONDATE) ON content0_.container = container1_.id INNER JOIN social_container container2_ ON container1_.parent_id = container2_.id WHERE container2_.parent_id = 215 ORDER BY container1_.creation_date DESC LIMIT 6; ------------------------------------------ Explain of the Query 1 SIMPLE container1_ index IDX_CONTAINER_CREATIONDATE 6 119 100.00 Using where 1 SIMPLE container2_ eq_ref PRIMARY,FK_87sftj2k6a1bucnj5ab2rs4s3 PRIMARY 8 tstMysql.container1_.parent_id 1 5.00 Using where 1 SIMPLE content0_ ref FK_gmhpacw2qahxt0gvpvc7243sc FK_gmhpacw2qahxt0gvpvc7243sc 9 tstMysql.container1_.id 1 100.00 Using index Suggested fix: We can't use "FORCE INDEX" because we are using an ORM (hibernate) which must be used on also ORACLE, SQL Server....