[mysql] Hilfe bei Query-Optimierung benötigt

[bastee]

Hallo!

ich verzweifle gerade an einer eigentlich recht simplen Query auf einer Tabelle. Das Problem bei der Query ist, das diese die komplette Tabelle (die leider sehr groß werden kann) durchsucht und ich keinen Weg finde, dies zu vermeiden.

Der relevante Ausschnitt aus der Query ist folgender:

SELECT FK_tag
FROM reference
WHERE FK_object in
 (SELECT fk_object
  FROM reference
  WHERE fk_tag = ?)

Die Tabelle sieht folgendermaßen aus:

create table Reference (
PK_id integer not null auto_increment,
fk_tag integer not null,
fk_object integer not null,
userId varchar(50) not null,
version timestamp not null,
constraint C_PK_Reference primary key (PK_id),
constraint c_t_o_u unique (userId, fk_object, fk_tag),
constraint c_fk_tag foreign key (fk_tag) references tag(pk_id),
constraint c_fk_object foreign key (fk_object) references object(pk_id),
index i_object_tag (fk_object, fk_tag)
) engine = innodb;

Trotz (verwendeter) Indizes muss die Query die ganze Tabelle durchsuchen ( explain select sagt mir, dass etwa so viele Datensätze durchsucht werden, wie vorhanden).

Lässt sich diese Query auch irgendwie ohne ein subselect schreiben? Gibt es noch einen besseren Index? Oder lässt sich diese Query tatsächlich nicht optimieren?

Für jede Hilfe wäre ich sehr dankbar!

Grüße, Basti

 

[Matthias Reitinger]

Hallo,

vielleicht so?

SELECT t1.FK_tag
FROM reference t1
INNER JOIN reference t2 ON (t1.FK_object = t2.FK_object AND t2.FK_tag = ?)

Grüße,
Matthias

 

[bastee]

Hallo Matthias,

das führt leider nicht zum gleichen Ergebnis, da nicht fk_tag und fk_objekt, sondern nur fk_tag, fk_object und userId zusammen unique sind.

So führt die Query zu zuvielen Ergebnissen.

Ich hatte es schon mal mit einem self-join probiert, bin aber auch immer zu diesem falschen Ergebnis gekommen. Irgendwie muss das doch möglich sein...

 

[bastee]

Also ich habe nochmal etwas weiter geforscht.

Der Grund, weshalb ein einfacher self-join nicht funktioniert, ist dass die zweite Tabelle möglicherweise mehrere Datensätze liefert wo sich nur die userId unterscheidet. Dies führt zu zuvielen Ergebniszeilen und damit zu einem falschen Ergebnis.

Die zweite Tabelle müsste im Grunde soetwas sein wie:

SELECT DISTINCT fk_tag, fk_object
FROM reference

Jetzt habe ich für diesen select mal eine view definiert:

CREATE VIEW distref AS
SELECT DISTINCT fk_tag, fk_object
FROM reference

Nun führe ich den join über die reference und die distref tabelle durch:

SELECT r.fk_tag
FROM reference r
INNER JOIN distref d ON (r.fk_object = d.fk_object and d.fk_tag = ?)

Nun stimmt das Ergebnis, und die Berechnung geht auch deutlich schneller.
ABER: Wenn ich mir den explain select anschaue, sehe ich das jetzt zwei mal über alle Tabellen gegangen wird! Wie kann die Berechnung da schneller sein Das kann doch nur an der Verwendung der Indizes liegen.
Ich kann aktuell nur mit 60.000 Datensätzen testen. Ich habe die Befürchtung, dass die Performance dieses selects bei +1mio und mehr wieder einbricht...

Hat noch Jemand eine Idee?

 

[bastee]

Der Grund für die bessere Performance der zweiten Query ist, dass hier der group-by über einen Index gemacht werden kann (den hatte ich noch angelegt).
Siehe hier: http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html.

Ich schätze, dies ist das Optimum für diese Art von Query. Schade, aber ist wohl so.

Ich lasse mich aber immer noch gerne eines besseren überzeugen ;-)

Kai