Sub-SELECT index problem in MySQL 4.1 (bk 2003-01-16)

>Description:
the following sub-select seems not to use the primary key-index
of table A:
(is significantly slower than replacing the sub-select with
a fixed IN()-list)

explain
select * from A
where ID IN (SELECT IDREF from B where dataB IN ('a1','a2','a3'));

>How-To-Repeat:
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");


# is correct (primary key is used)

mysql> explain select * from A where ID IN (1,2);
=20
+----+-------------+-------+-------+---------------+-------- -+---------+-=
-----+------+-------------+
| id | select_type | table | type | possible_keys | key | key_le=
n |=20
ref | rows | Extra |
=20
+----+-------------+-------+-------+---------------+-------- -+---------+-=
-----+------+-------------+
| 1 | SIMPLE | A | range | PRIMARY | PRIMARY | =
4 |=20
NULL | 2 | Using where |
=20
+----+-------------+-------+-------+---------------+-------- -+---------+-=
-----+------+-------------+
1 row in set (0.00 sec)

# is correct

mysql> explain select IDREF from B where dataB IN ('a1','a2','a3');
=20
+----+-------------+-------+-------+---------------+-------+ ---------+---=
---+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len =
| ref =20
| rows | Extra |
=20
+----+-------------+-------+-------+---------------+-------+ ---------+---=
---+------+-------------+
| 1 | SIMPLE | B | range | dataB | dataB | 255 =
|=20
NULL | 3 | Using where |
=20
+----+-------------+-------+-------+---------------+-------+ ---------+---=
---+------+-------------+
1 row in set (0.00 sec)

# row 1 is INCORRECT (primary key is unused)

mysql> explain select * from A where ID IN (SELECT IDREF from B where=
=20
dataB IN ('a1','a2','a3'));
=20
+----+---------------------+-------+-------+---------------+ -------+-----=
----+------+------+-------------+
| id | select_type | table | type | possible_keys | key |=20
key_len | ref | rows | Extra |
=20
+----+---------------------+-------+-------+---------------+ -------+-----=
----+------+------+-------------+
| 1 | PRIMARY | A | ALL | NULL | NULL | =
=20
NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBSELECT | B | range | dataB | dataB | =
=20
255 | NULL | 3 | Using where |
=20
+----+---------------------+-------+-------+---------------+ -------+-----=
----+------+------+-------------+
2 rows in set (0.00 sec)
>Fix:
don't know
=20
>Submitter-Id:=09
>Originator:=09ck@newsclub.de
>Organization: NewsClub.de
>MySQL support: none
>Synopsis:=09Sub-SELECT index problem in MySQL 4.1
>Severity:=09non-critical
>Priority:=09medium
>Category:=09mysql
>Class: sw-bug
>Release:=09mysql-4.1.0-alpha-011603 (Source distribution)

>Environment:
=09
System: Linux fa-ls018-01 2.4.19-4GB #1 Fri Sep 13 13:19:15 UTC 2002 i686=
=20
unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc=20
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/3.2/specs
Configured with: ../configure --enable-threads=3Dposix --prefix=3D/usr=20
--with-local-prefix=3D/usr/local --infodir=3D/usr/share/info=20
--mandir=3D/usr/share/man --libdir=3D/usr/lib=20
--enable-languages=3Dc,c++,f77,objc,java,ada --enable-libgcj=20
--with-gxx-include-dir=3D/usr/include/g++ --with-slibdir=3D/lib=20
--with-system-zlib --enable-shared --enable-__cxa_atexit i486-suse-linux
Thread model: posix
gcc version 3.2
Compilation info: CC=3D'gcc' CFLAGS=3D'-O2 -mcpu=3Dpentiumpro' CXX=3D'g=
cc' =20
CXXFLAGS=3D'-O2 -mcpu=3Dpentiumpro -felide-constructors' LDFLAGS=3D'' A=
SFLAGS=3D''
LIBC:=20
-rwxr-xr-x 1 root root 1312470 2002-10-15 19:19 /lib/libc.so.=
6
-rw-r--r-- 1 root root 23159816 2002-10-08 18:00 /usr/lib/libc=
a
-rw-r--r-- 1 root root 178 2002-10-08 18:00 /usr/lib/libc=
so
lrwxrwxrwx 1 root root 20 2002-10-22 09:26=20
/usr/lib/libc-client.so -> libc-client.so.2001a
-rwxr-xr-x 1 root root 735696 2002-09-09 22:47=20
/usr/lib/libc-client.so.2001a
Configure command: ./configure '--prefix=3D/usr/local/mysql'=20
'--with-extra-charsets=3Dcomplex' '--enable-thread-safe-client'=20
'--enable-local-infile' '--enable-assembler' '--disable-shared'=20
'--with-client-ldflags=3D-all-static' '--with-mysqld-ldflags=3D-all-stati=
c'=20
'CFLAGS=3D-O2 -mcpu=3Dpentiumpro' 'CXXFLAGS=3D-O2 -mcpu=3Dpentiumpro=20
-felide-constructors' 'CXX=3Dgcc'


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13503@lists.mysql.com
To unsubscribe, e-mail

Re: Sub-SELECT index problem in MySQL 4.1 (bk 2003-01-16)

Christian =?iso-8859-15?q?Kohlschütter?= writes:
> >Description:
> the following sub-select seems not to use the primary key-index
> of table A:
> (is significantly slower than replacing the sub-select with
> a fixed IN()-list)
>
> explain
> select * from A
> where ID IN (SELECT IDREF from B where dataB IN ('a1','a2','a3'));
>
> >How-To-Repeat:
>

Hi!

I have tested your test case and I have to inform you that subselect
truly utilizes a key in the query execution.

A query with fixed IN() list, that returns the same number of tuples,
will always be faster then any subselect, because much more code has
to be executed, plus as in your case, one more additional table has to
be opened, searched and closed.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13521@lists.mysql.com
To unsubscribe, e-mail

Re: Sub-SELECT index problem in MySQL 4.1 (bk 2003-01-16)

Sinisa Milivojevic writes
> Christian =3D?iso-8859-15?q?Kohlsch=3DFCtter?=3D writes:
> > >Description:
> > the following sub-select seems not to use the primary key-index
> > of table A:
> > (is significantly slower than replacing the sub-select with
> > a fixed IN()-list)
> >=20
> > explain
> > select * from A
> > where ID IN (SELECT IDREF from B where dataB IN ('a1','a2','a3=
'));
> >=20
> > >How-To-Repeat:
>=20
> Hi!
>=20
> I have tested your test case and I have to inform you that subselect
> truly utilizes a key in the query execution.
>=20
> A query with fixed IN() list, that returns the same number of tuples,
> will always be faster then any subselect, because much more code has
> to be executed, plus as in your case, one more additional table has to
> be opened, searched and closed.

Hello Sinisa,

sorry, but I do not get the point.
I have a test database with 400,000 entries in table B (inner table), 169=
,410=20
entries in table A (outer table).

When I first select from B, and manually put the results into another que=
ry=20
for A, it is _much_ faster than using sub-select. I do not think that it'=
s=20
just the code overhead.

EXPLAIN output:

explain select * from B where ID IN (22395,43790);
+----+-------------+---------+-------+---------------+------ ---+---------=
+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len =
| ref =20
| rows | Extra |
+----+-------------+---------+-------+---------------+------ ---+---------=
+------+------+-------------+
| 1 | SIMPLE | B | range | PRIMARY | PRIMARY | 8 | NULL=
| =20
2 | Using where |
+----+-------------+---------+-------+---------------+------ ---+---------=
+------+------+-------------+

explain select * from A where ID IN (select IDREF from B where code IN=20
('someCode'));
+----+---------------------+----------------+-------+------- --------+----=
--+---------+------+--------+-------------------------=20
-+
| id | select_type | table | type | possible_keys | key=
|=20
key_len | ref | rows | Extra |
+----+---------------------+----------------+-------+------- --------+----=
--+---------+------+--------+-------------------------=20
-+
| 1 | PRIMARY | A | ALL | NULL | NULL | =
NULL=20
| NULL | 169410 | Using where |
| 2 | DEPENDENT SUBSELECT | B | range | SCI | SCI | 12 |=
NULL=20
| 1 | Using where; Using index |
+----+---------------------+----------------+-------+------- --------+----=
--+---------+------+--------+-------------------------=20
-+

As you can see, the cardinality is 2 with a simple IN(...) versus 169410 =
(=
full scan) with a subselect. (and "possible_keys" is NULL for the=20
sub-select!!!)
--=20
Christian Kohlschütter
ck@newsclub.de

http://www.newsclub.de - Der Meta-Nachrichten-Dienst


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13524@lists.mysql.com
To unsubscribe, e-mail

Re: Sub-SELECT index problem in MySQL 4.1 (bk 2003-01-16)

Sorry, there was a typo in the last post. The first explain-command was f=
or=20
table "A", not "B" (I replaced the real names for readability ;-)

so, the corrected statement is:

> explain select * from A where ID IN (22395,43790);
> +----+-------------+---------+-------+---------------+------ ---+-------=
--+-
>-----+------+-------------+
>
> | id | select_type | table | type | possible_keys | key | key_le=
n |
> | ref rows | Extra |
>
> +----+-------------+---------+-------+---------------+------ ---+-------=
--+-
>-----+------+-------------+
>
> | 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 8 | NU=
LL |
>
> 2 | Using where |
> +----+-------------+---------+-------+---------------+------ ---+-------=
--+-
>-----+------+-------------+
>

--=20
Christian Kohlschütter
ck@newsclub.de

http://www.newsclub.de - Der Meta-Nachrichten-Dienst


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13525@lists.mysql.com
To unsubscribe, e-mail

Re: Sub-SELECT index problem in MySQL 4.1 (bk 2003-01-16)

Hi!

On Mon, Jan 20, 2003 at 11:40:22AM +0100, Christian Kohlsch?tter wrote:
> Sorry, there was a typo in the last post. The first explain-command was for
> table "A", not "B" (I replaced the real names for readability ;-)
>
> so, the corrected statement is:
>
> > explain select * from A where ID IN (22395,43790);
> > +----+-------------+---------+-------+---------------+------ ---+---------+-
> >-----+------+-------------+
> >
> > | id | select_type | table | type | possible_keys | key | key_len |
> > | ref rows | Extra |
> >
> > +----+-------------+---------+-------+---------------+------ ---+---------+-
> >-----+------+-------------+
> >
> > | 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 8 | NULL |
> >
> > 2 | Using where |
> > +----+-------------+---------+-------+---------------+------ ---+---------+-
> >-----+------+-------------+
> >

Thank you for your report.
Subselect for now have not so much optimization. We only start to do it.

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13526@lists.mysql.com
To unsubscribe, e-mail