Sub-SELECT index problem in MySQL 4.1 remains (4.1.0-alpha-040303-max)
am 05.04.2003 15:59:52 von ckHello,
>Description:
There is still a bug in MySQL 4.1 concerning the usage/non-usage of indexes=
with sub-selects
(described on 17 January 2003 in this list for the first time)
I have provided some more examples.
>How-To-Repeat:
Create a simple 1-n data structure:
DROP TABLE IF EXISTS A;
CREATE TABLE A (
ID int unsigned not null,
dataA varchar(255) not null,
PRIMARY KEY(ID)
);
INSERT INTO A VALUES (1, "Foo");
INSERT INTO A VALUES (2, "Bar");
DROP TABLE IF EXISTS B;
CREATE TABLE B (
IDREF int unsigned not null,
dataB varchar(255) not null,
KEY (dataB)
);
INSERT INTO B VALUES (1, "To-Foo1");
INSERT INTO B VALUES (1, "To-Foo2");
INSERT INTO B VALUES (1, "To-Foo3");
INSERT INTO B VALUES (1, "To-Foo4");
INSERT INTO B VALUES (1, "To-Foo5");
INSERT INTO B VALUES (1, "To-Foo6");
INSERT INTO B VALUES (1, "To-Foo7");
INSERT INTO B VALUES (1, "To-Foo8");
INSERT INTO B VALUES (1, "To-Foo9");
INSERT INTO B VALUES (1, "To-Foo10");
INSERT INTO B VALUES (2, "To-Bar1");
INSERT INTO B VALUES (2, "To-Bar2");
INSERT INTO B VALUES (2, "To-Bar3");
INSERT INTO B VALUES (2, "To-Bar4");
INSERT INTO B VALUES (2, "To-Bar5");
INSERT INTO B VALUES (2, "To-Bar6");
INSERT INTO B VALUES (2, "To-Bar7");
INSERT INTO B VALUES (2, "To-Bar8");
INSERT INTO B VALUES (2, "To-Bar9");
INSERT INTO B VALUES (2, "To-Bar10");
Now let's explain some SELECT statements:
The following works as expected (rows to check: 1 / 1)
mysql> explain select A.* from A, B WHERE A.ID =3D B.IDREF AND B.DataB=3D'T=
o-Bar10';
+----+-------------+-------+--------+---------------+------- --+---------+--=
=2D------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | r=
ef | rows | Extra |
+----+-------------+-------+--------+---------------+------- --+---------+--=
=2D------+------+-------------+
| 1 | SIMPLE | B | ref | dataB | dataB | 255 | c=
onst | 1 | Using where |
| 1 | SIMPLE | A | eq_ref | PRIMARY | PRIMARY | 4 | B=
IDREF | 1 | |
+----+-------------+-------+--------+---------------+------- --+---------+--=
=2D------+------+-------------+
2 rows in set (0.01 sec)
The following works, too (also 1 / 1), difference: different order and type=
=3Dref instead of eq_ref
mysql> explain select * from a where ID =3D (SELECT IDREF FROM B WHERE Data=
B=3D'To-Bar10');
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D---+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | re=
f | rows | Extra |
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D---+------+-------------+
| 1 | PRIMARY | A | const | PRIMARY | PRIMARY | 4 | co=
nst | 1 | |
| 2 | SUBSELECT | B | ref | dataB | dataB | 255 | =
| 1 | Using where |
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D---+------+-------------+
2 rows in set (0.00 sec)
Whereas the following does NOT use indexes properly (type =3D ALL, no possi=
ble_keys)
And I do not understand why that should be a _dependent_
subselect.
mysql> explain select * from a where ID IN (SELECT IDREF FROM B WHERE DataB=
=3D'To-Bar10');
+----+---------------------+-------+------+---------------+- ------+--------=
=2D+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len=
| ref | rows | Extra |
+----+---------------------+-------+------+---------------+- ------+--------=
=2D+-------+------+-------------+
| 1 | PRIMARY | A | ALL | NULL | NULL | NULL=
| NULL | 2 | Using where |
| 2 | DEPENDENT SUBSELECT | B | ref | dataB | dataB | 255=
| const | 1 | Using where |
+----+---------------------+-------+------+---------------+- ------+--------=
=2D+-------+------+-------------+
2 rows in set (0.00 sec)
I suppose that the subselects are not evaluated correctly - is it possible =
that the primary table gets
evaluated before the table in the sub-select?
Moreover, the following constant SELECTs are all differently evaluated. Per=
haps this is also related to the bug:
mysql> explain select * from a where ID IN (1);
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D--+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | re=
f | rows | Extra |
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D--+------+-------------+
| 1 | SIMPLE | a | range | PRIMARY | PRIMARY | 4 | NU=
LL | 1 | Using where |
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D--+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from a where ID IN (SELECT 1);
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D---+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | re=
f | rows | Extra |
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D---+------+-------+
| 1 | PRIMARY | a | const | PRIMARY | PRIMARY | 4 | co=
nst | 1 | |
+----+-------------+-------+-------+---------------+-------- -+---------+---=
=2D---+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from a where ID IN (SELECT 1 FROM B);
+----+---------------------+-------+-------+---------------+ -------+-------=
=2D-+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_le=
n | ref | rows | Extra |
+----+---------------------+-------+-------+---------------+ -------+-------=
=2D-+------+------+--------------------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NUL=
L | NULL | 2 | Using where |
| 2 | DEPENDENT SUBSELECT | B | index | NULL | dataB | 25=
5 | NULL | 19 | Using where; Using index |
+----+---------------------+-------+-------+---------------+ -------+-------=
=2D-+------+------+--------------------------+
2 rows in set (0.00 sec)
Best regards,
=2D-=20
Christian Kohlschütter
ck@newsclub.de
http://www.newsclub.de - Der Meta-Nachrichten-Dienst
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org