Bug #14253 Optimizer uses partial indexes for subqueries, where it should not
Submitted: 24 Oct 2005 9:26 Modified: 31 Dec 2009 12:44
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0, 4.1, 6.0.14-bzr OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[24 Oct 2005 9:26] Domas Mituzas
Description:
Optimizer chooses to use partial indexes in subquery, if range conditions exist, that suits only some values. Overall performance is degraded multiple times because of this decision.

How to repeat:
SELECT 1 FROM t1 WHERE EXISTS (
	SELECT 1 FROM t2 WHERE t1.m_id=t2.m_id 
	AND t2.a = 2 AND b IN (1,2,3)
)

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: pk
      key_len: 8
          ref: NULL
         rows: 10000
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: ref
possible_keys: m1_a_b
          key: m1_a_b
      key_len: 6
          ref: t1.m_id.m_id,const
         rows: 3
        Extra: Using where; Using index
2 rows in set (0.00 sec)

Though, if we supply ref for 'b', then we get full index used and much better performance:

SELECT 1 FROM t1 WHERE EXISTS (
	SELECT 1 FROM t2 WHERE t1.m_id=t2.m_id 
	AND t2.a = 2 AND b IN (1)
)

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: pk
      key_len: 8
          ref: NULL
         rows: 10000
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: ref
possible_keys: m1_a_b
          key: m1_a_b
      key_len: 9
          ref: t1.m_id.m_id,const,const
         rows: 3
        Extra: Using where; Using index
2 rows in set (0.00 sec)

Suggested fix:
Use full index for range scans in subqueries...
[25 Oct 2005 16:03] Domas Mituzas
SELECT a FROM t1 WHERE EXISTS (SELECT b FROM t2 WHERE a=b AND c IN (10,20))

Attachment: 14253.gz (application/x-gzip, text), 193.87 KiB.

[9 Sep 2006 21:23] Igor Babaev
The same deficiency of the current optimizer can be demonstrated with just a join:

mysql> EXPLAIN SELECT a FROM t1,t2 WHERE a=b AND c IN (10,20);
+----+-------------+-------+------+---------------+------+---------+-----------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows  | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-----------+-------+--------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      | 10002 |                          |
|  1 | SIMPLE      | t2    | ref  | b             | b    | 5       | test.t1.a |    11 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-----------+-------+--------------------------+
2 rows in set (0.00 sec)

We need here a combination of the 'ref' access method for the first component and the 'range' access method for the second component of the index. 
Yet this would require a new serious development.
[31 Dec 2009 12:44] Valeriy Kravchuk
This problem is still repeatable with recent 6.0.14 from bzr:

77-52-7-73:6.0-codebase openxs$ 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 3
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  EXPLAIN SELECT a FROM t1,t2 WHERE a=b AND c IN (10,20);
+----+-------------+-------+------+---------------+------+---------+-----------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows  | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-----------+-------+--------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      | 10002 | Using where              |
|  1 | SIMPLE      | t2    | ref  | b             | b    | 5       | test.t1.a |    11 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-----------+-------+--------------------------+
2 rows in set (0.00 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  KEY `b` (`b`,`c`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)