MySQL Bugs: #17560: select with subselect does not use indexed key

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:	Miguel Solorzano	CPU Architecture:	Any

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

Hi!

Can you please provide us with the output of the EXPLAIN for your queries ?

Thanks

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.

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.

I am facing the same issue. Is there any resolution for it?

This problem will be resolved in 5.2 when WL #2980 is done.

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>

Miguel,

  Could you please verify whether this has been fixed in the mysql-next-mr-opt-backporting tree?

Thanks,
Manyi

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)