Bug #17560 | select with subselect does not use indexed key | ||
---|---|---|---|
Submitted: | 19 Feb 2006 20:36 | Modified: | 17 Jun 2010 14:40 |
Reporter: | Alois Treindl | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0.18 | OS: | Linux (Linux) |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[19 Feb 2006 20:36]
Alois Treindl
[20 Feb 2006 16:03]
Jorge del Conde
Hi! Can you please provide us with the output of the EXPLAIN for your queries ? Thanks
[20 Feb 2006 16:48]
Alois Treindl
here is the explain output: explain select * from cid where uid = any (select uid from adr where snam like 'xxxx'); +----+--------------------+-------+-----------------+----------------+---------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-----------------+----------------+---------+---------+------+---------+--------------------------+ | 1 | PRIMARY | cid | ALL | NULL | NULL | NULL | NULL | 3901260 | Using where | | 2 | DEPENDENT SUBQUERY | adr | unique_subquery | PRIMARY,adrnam | PRIMARY | 4 | func | 1 | Using index; Using where | +----+--------------------+-------+-----------------+----------------+---------+---------+------+---------+--------------------------+ The problem is the ALL for the search in the primary table (cid). It does not use the existing index 'cidx1' which automatically exists for the unique key column 'uid'. Even creating an extra index on 'uid' does not change the behaviour of the optimizer, which does a table scan instead of using the index. Also writing the query with 'force index' does not help, the query plan remains the same, when I use: explain select cid,uid from cid force index (cidx1) where uid = any (select uid from adr where snam like 'xxxx'); Of course I know that I could write this particular query differently, without a subselect, and then it performs normally. I just had upgraded from 4.0 to 5.0 (among other reasons, because subselects are available) and wanted to try out one of them. But it seems to me that the query plannign for the primary query is very poor, as soon as it has to deal with output from subselect.
[31 May 2006 22:12]
Jorge del Conde
Hi! Thanks for your bug report. I was able to reproduce this under 5.0.23bk. The results for EXPLAIN that I received where exactly the same as yours.
[23 Jan 2007 19:24]
Yogish Baliga
I am facing the same issue. Is there any resolution for it?
[14 Mar 2007 22:42]
Igor Babaev
This problem will be resolved in 5.2 when WL #2980 is done.
[28 Jul 2009 23:04]
MySQL Verification Team
Closing this bug since the query involving subselect are fixed in 6.0 server version, see below to compare 5.0 with 6.0: miguel@lara:~/dbs$ 5.0/bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.85-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from cid where uid = any (select uid from adr where sfnm like 'Treindl'); +---------+-------------------------------------+ | uid | cid | +---------+-------------------------------------+ | 1048577 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | +---------+-------------------------------------+ 1 row in set (1 min 22.87 sec) mysql> miguel@lara:~/dbs$ 6.0/bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 6.0.12-alpha-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from cid where uid = any (select uid from adr where sfnm like 'Treindl'); +---------+-------------------------------------+ | uid | cid | +---------+-------------------------------------+ | 1048577 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | +---------+-------------------------------------+ 1 row in set (2.04 sec) mysql>
[9 Jun 2010 9:13]
Manyi Lu
Miguel, Could you please verify whether this has been fixed in the mysql-next-mr-opt-backporting tree? Thanks, Manyi
[17 Jun 2010 14:40]
MySQL Verification Team
Yes according the results looks is already fixed: mysql> show variables like "%version%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | protocol_version | 10 | | version | 5.0.92 | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +-------------------------+---------------------+ 5 rows in set (0.00 sec) mysql> select * from cid where uid = any (select uid from adr where sfnm like 'Treindl'); +--------+-------------------------------------+ | uid | cid | +--------+-------------------------------------+ | 104577 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | +--------+-------------------------------------+ 1 row in set (7.10 sec) mysql> show variables like "%version%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | innodb_version | 1.0.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.99-m4 | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------+ 7 rows in set (0.00 sec) mysql> select * from cid where uid = any (select uid from adr where sfnm like 'Treindl'); +--------+-------------------------------------+ | uid | cid | +--------+-------------------------------------+ | 104577 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | +--------+-------------------------------------+ 1 row in set (0.06 sec)