dependent subquery

Hallo!

Ich habe hier eine Query mit nested select. M.E. sollte die subquery
unabhängig sein, ist sie für die Datenbank aber nicht. Dadurch dauert die
Query sehr viel länger, als sie sollte.

Ich habe folgende zwei Tabellen:

create table taba (l varchar(30), r varchar(30));
create table tabb (pk varchar(30) PRIMARY KEY, dat varchar(30));

Auf der ersten richte ich noch zwei Indizes ein:

create index tabal on taba (l);
create index tabar on taba (r);

Und dann fülle ich beide Tabellen mit jeweils drei Zeilen:

insert into taba (l,r) values ('left1','right1')
insert into taba (l,r) values ('left2','right2')
insert into taba (l,r) values ('left3','right3')

insert into tabb (pk, dat) values ('right1', 'dat1')
insert into tabb (pk, dat) values ('right2', 'dat2')
insert into tabb (pk, dat) values ('right3', 'dat3')

Jetzt kommt meine Query:

select tabb.pk from tabb
where tabb.pk in
( select taba.r from taba where taba.l = 'left1' )
and tabb.dat like 'dat%'

Die liefert erwartungsgemäß eine Zeile mit "right1". Unerwartet is dagegen
der Execution Plan:

select_type tab type pos_keys key kl ref r Extra
1 PRIMARY tabb ALL 92 3 U. where/ind
2 DEPENDENT SUBQUERY taba ref tabal,tabar tabal 93 const 1 U. where

Die Datenbank meint, das inner select wäre anhängig vom äußeren, obwohl es
das doch offensichtlich nicht der Fall ist. Wenn ich die Beispieldaten
größer mache, dauert dann auch die Query sehr lange. Meine einzige
Erklärung dafür ist die Abhängigkeit des Sub-Selects. Wenn das als
unabhängig erkannt würde, dann sollte die Query in Null-Komma-Nichts
durchgehen, egal wie groß die Daten sind.

Ich verwende MySQL 5.0.24a und den JDBC Driver 3.1.12.

Hat vielleicht jemand einen Tipp?
Ralf.

Re: dependent subquery

ralf@rw7.de schrieb:

> Ich habe hier eine Query mit nested select. M.E. sollte die subquery
> unabhängig sein, ist sie für die Datenbank aber nicht. Dadurch dauert die
> Query sehr viel länger, als sie sollte.

> select_type tab type pos_keys key kl ref r Extra
> 1 PRIMARY tabb ALL 92 3 U. where/ind
> 2 DEPENDENT SUBQUERY taba ref tabal,tabar tabal 93 const 1 U. where

> Die Datenbank meint, das inner select wäre anhängig vom äußeren, obwohl es
> das doch offensichtlich nicht der Fall ist.

> Ich verwende MySQL 5.0.24a und den JDBC Driver 3.1.12.

Re: dependent subquery

>
>

Vielen Dank für den Hinweis. Das Problem schien mir zu grundlegend, als das
ich den Fehler bei MySQL vermutet hätte. Ich dachte, man muss nur irgendwas
magisches mit der Query machen, und dann funktioniert es.

Meine Lösung (besser gesagt Workaround) ist, das Statement in zwei
aufzuspalten, d.h. das Zwischenergebnis des inner-selects durch den Client
zu schleifen. Diese Ergebnismenge ist bei mir immer klein, und bringt daher
die erhoffte erhebliche Verbesserung.

Vielen Dank nochmal,
Ralf.

Re: dependent subquery

On 09.01.2007 12:30, ralf@rw7.de wrote:
> Hallo!
>
> Ich habe hier eine Query mit nested select. M.E. sollte die subquery
> unabhängig sein, ist sie für die Datenbank aber nicht. Dadurch dauert die
> Query sehr viel länger, als sie sollte.
>
> Ich habe folgende zwei Tabellen:
>
> create table taba (l varchar(30), r varchar(30));
> create table tabb (pk varchar(30) PRIMARY KEY, dat varchar(30));
>
> Auf der ersten richte ich noch zwei Indizes ein:
>
> create index tabal on taba (l);
> create index tabar on taba (r);
>
> Und dann fülle ich beide Tabellen mit jeweils drei Zeilen:
>
> insert into taba (l,r) values ('left1','right1')
> insert into taba (l,r) values ('left2','right2')
> insert into taba (l,r) values ('left3','right3')
>
> insert into tabb (pk, dat) values ('right1', 'dat1')
> insert into tabb (pk, dat) values ('right2', 'dat2')
> insert into tabb (pk, dat) values ('right3', 'dat3')
>
> Jetzt kommt meine Query:
>
> select tabb.pk from tabb
> where tabb.pk in
> ( select taba.r from taba where taba.l = 'left1' )
> and tabb.dat like 'dat%'

Kannst du das nicht mit einem einfachen Join erschlagen?

select tabb.pk
from tabb, taba
where tabb.pk = taba.r
and taba.l = 'left1'
and tabb.dat like 'dat%'

Wenn du doppelte vermeiden willst, kannst du noch DISTINCT oder GROUP BY
einsetzen. (Ist uU nicht nötig je nach Daten bzw. Constraints.)

mysql> explain select tabb.pk
-> from tabb, taba
-> where tabb.pk = taba.r
-> and taba.l = 'left1'
-> and tabb.dat like 'dat%'
-> ;
+----+-------------+-------+------+---------------+--------- +---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+------+---------------+--------- +---------+------------+------+-------------+
| 1 | SIMPLE | taba | ref | tabal,tabar | tabal | 33 |
const | 1 | Using where |
| 1 | SIMPLE | tabb | ref | PRIMARY | PRIMARY | 32 |
st1.taba.r | 1 | Using where |
+----+-------------+-------+------+---------------+--------- +---------+------------+------+-------------+
2 rows in set (0.00 sec)


mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.0.27-Debian_1-log |
+---------------------+

Habe ich was übersehen?

> Die liefert erwartungsgemäß eine Zeile mit "right1". Unerwartet is dagegen
> der Execution Plan:
>
> select_type tab type pos_keys key kl ref r Extra
> 1 PRIMARY tabb ALL 92 3 U. where/ind
> 2 DEPENDENT SUBQUERY taba ref tabal,tabar tabal 93 const 1 U. where
>
> Die Datenbank meint, das inner select wäre anhängig vom äußeren, obwohl es
> das doch offensichtlich nicht der Fall ist. Wenn ich die Beispieldaten
> größer mache, dauert dann auch die Query sehr lange. Meine einzige
> Erklärung dafür ist die Abhängigkeit des Sub-Selects. Wenn das als
> unabhängig erkannt würde, dann sollte die Query in Null-Komma-Nichts
> durchgehen, egal wie groß die Daten sind.
>
> Ich verwende MySQL 5.0.24a und den JDBC Driver 3.1.12.

Der JDBC-Treiber hat nix damit zu tun.

Ciao

robert

Re: dependent subquery

Ralf Wiebicke wrote:

> Meine Lösung (besser gesagt Workaround) ist, das Statement in zwei
> aufzuspalten, d.h. das Zwischenergebnis des inner-selects durch den
> Client zu schleifen. Diese Ergebnismenge ist bei mir immer klein, und
> bringt daher die erhoffte erhebliche Verbesserung.

Würde ich dennoch nicht tun. Besser: entweder Temporary Table oder (wie
Robert vorschlägt) Join.

In beiden Fällen bleiben die Daten im Server -> weniger Overhead.
Vielleicht wächst die Datenmenge ja doch mal enorm an?


Thomas
--
Jabber-ID: glglgl@amessage.info (keine Email-Adresse!)
Warum Jabber, was ist das und wie geht das?
http://de.wikibooks.org/wiki/Jabber-Kompendium:_Schnelleinst ieg

Re: dependent subquery

Hallo Robert!

> Kannst du das nicht mit einem einfachen Join erschlagen?

Danke für den Hinweis.

Einen Join hatte ich ursprünglich. Meine Probleme hab ich leider immer mit
Datenbanken, die Kundendaten enthalten. Daher muss ich, wenn ich hier
posten will, meine konkreten Beispiele in "harmlose" verwandeln.

Meine ursprüngliche Query mit den Joins ist auch schrecklich langsam, aber
das anonymisierte Beispiel, das ich für die News entwickeln wollte, war
immer schnell. Ist mir ein Rätsel - ich hab mich wirklich sehr angestrengt.

Dann kam mir die Idee mit dem nested select - das war leider genauso
langsam, aber das anonymisierte Beispiel wenigstens auch.

>> Ich verwende MySQL 5.0.24a und den JDBC Driver 3.1.12.
> Der JDBC-Treiber hat nix damit zu tun.
Poste ich aus Prinzip. Man weiß ja nie.

Viele Grüße,
Ralf.

Re: dependent subquery

Hallo Thomas!

> Würde ich dennoch nicht tun. Besser: entweder Temporary Table oder (wie
> Robert vorschlägt) Join.

Danke für den Hinweis. Temporäre Tabellen kannte ich noch gar nicht.

> In beiden Fällen bleiben die Daten im Server -> weniger Overhead.
> Vielleicht wächst die Datenmenge ja doch mal enorm an?

Das sollte sowieso nicht passieren. Wenn die innere Query eine große
Ergebnismenge liefert, wird auch die äußere Query langsam. Natürlich hat
man da mit temporären Tabellen einen längeren Atem, als wenn man das
Ergebnis durch den Client schleift. Aber das hilft halt nur ein Stück weit.

Viele Grüße,
Ralf.