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:
None 
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
Description:
I have a table 'cid' with unique numerical index 'uid', it has 4 mill. records.
I have a table 'adr' with unique numerical index 'uid', it has 300'000 records.
select * from cid where uid in (2956460,500006);
+---------+---------------------------+------------+
| uid     | cid                       
+---------+---------------------------+------------+
|  500006 | 6d5aaaa15378-i9665
| 2956460 | 1dnfileC0pLke-u102
2 rows in set (0.00 sec)

select uid from adr where snam like 'xxxx';
+---------+
| uid     |
+---------+
|  500006 |
| 2956460 |
+---------+
2 rows in set (0.00 sec)

But a select with subselect is extremely slow, it seems to scan the while table cid, instead
of using the result of the subselect:
select * from cid where uid = any (select uid from adr where snam like 'Treindl');
+---------+---------------------------+------------+
| uid     | cid                       | 
+---------+---------------------------+------------+
| 2956460 | 1dnfileC0pLke-u10298
|  500006 | 6d5aaaa15378-i9665
+---------+---------------------------+------------+
2 rows in set (16.82 sec)

Table definitions, as far as relevant here:
describe cid;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| uid   | int(11)  | NO   | UNI | 0       |       |
| cid   | char(35) | NO   | PRI |         |       |

describe adr;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(11)     | NO   | PRI | 0       |       |
| sfnm  | varchar(40) | NO   |     |         |       |

Maybe I need to add that I have recently upgraded mysql from 4.0 to 5.0, and
the tables were not dumped in the upgrade.

But they have been optimized under 5.0 with 'optimize table' statements.

How to repeat:
as described

Suggested fix:
fix query optimizer
[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)