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: | |
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 |
[24 Oct 2005 9:26]
Domas Mituzas
[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)