MySQL Performance von Self Joins

[baserider]

Hi,

ich habe eine etwas größere Tabelle (derzeit über 100.000 Einträge). Index ist gesetzt und bei einem Self Join dauert eine Abfrage jedoch mittlerweile ziemlich lange. Hinzu kommt, das es einen Join auf eine weitere Tabelle gibt.

Gibt es denn Möglichkeiten die Performance bei einem self join einer großen Tabelle zu verbessern (oder andere wege um das gleiche Ergebnis zu erhalten)?

 

[Darklord]

Das Problem ist viel zu ungenau geschildert.
Eine Datenbank mit 100000 Einträgen würde ich nicht als groß bezeichnen.

Welche Datenbank Engine verwendest du und wie sieht die Struktur der Tabellen aus? Und wie überhaupt die Query?

Mit dem SQL Befehl

EXPLAIN

kann man sich ein paar Informationen zu dem Query ausgeben lassen.

Willkürlich einen index setzen macht auch wenig Sinn.

Um deine Frage zu beantworten. Ja, kommt drauf an.

 

[ice-breaker]

Das Problem ist viel zu ungenau geschildert.

Korrekt, es kann nämlich alles bedeuten (Query und DB-Struktur nötig!).
So könnte ich zb annehmen, dass du einen Self-Join ohne Bedingung machst:

SELECT ...
FROM tab AS a
JOIN tab AS b

Das wäre ein kartesisches Produkt, also 100k mal 100k Datensätze, da wäre es kein Wunder, wenn Ewigkeiten vergehen

---

Ein Self-Join ist auch nur ein Join und hat keine schlechtere Performance als jeder andere Join.

 

[baserider]

Nach Ausführung von Explain erhalte ich folgendes:

id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
1|SIMPLE|pp|index|idx_pid, produktvergleich_id, idx_pid_produktverglei...|idx_pid_produktvergleich_id|8| NULL|121858|Using index; Using temporary; Using filesort
1| SIMPLE| k| eq_ref| PRIMARY| PRIMARY| 4| db.pp.pid | 1
1| SIMPLE| pp2| ref| idx_pid, produktvergleich_id, idx_pid_produktverglei... | idx_pid_produktvergleich_id | 4 | db.pp.produktvergleich_id| 3 | Using where; Using index
1 | SIMPLE| k2 | eq_ref | PRIMARY | PRIMARY| 4 | db.pp2.pid | 1

Ist es denn korrekt wenn da in der ersten Zeile bei rows 121858 steht?
Das ist die Abfrage: (dauert über 5 Sekunden)

EXPLAIN SELECT 
            k.name,
            k2.name      
        FROM 
            table1 pp
        INNER JOIN 
            table1 pp2
        ON 
            pp2.produktvergleich_id = pp.produktvergleich_id
        INNER JOIN 
            table2 k 
        ON 
            k.pid = pp.pid
        INNER JOIN 
            table2 k2 
        ON 
            k2.pid = pp2.pid
        WHERE 
            pp.pid != pp2.pid 
        AND 
            pp.pid < pp2.pid
        GROUP BY 
            pp2.pid, pp.pid
        ORDER BY 
            COUNT(pp2.pid) DESC
        LIMIT 0,10

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `produktvergleich_id` int(11) NOT NULL,
  `pid` int(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pid` (`pid`),
  KEY `produktvergleich_id` (`produktvergleich_id`),
  KEY `idx_pid_produktvergleich_id` (`produktvergleich_id`,`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=121932 ;

 

[tobomator]

ich kann dir da nicht weiter helfen.
Mir fehlt schon das verständnis dafür, warum du einen Index erstellst für das Suchen, wo du eins mit sich selbst als Kriterium für den Index festlegst...

...
KEY `produktvergleich_id` (`produktvergleich_id`),
...

desweiteren sind hier nicht alle beteiligten Tabellen angegen, nur eine.
Daraus kann man nicht nachvollziehen, inwieweit hier irgendwo was ne Bremse ist ...
Aber wahrscheinlicher wird wohl eher Dein gesamtes DB Konstrukt die Ursache dafür sein...

 

[ice-breaker]

Es müssen 120k Datensätze auf mehrere Tabellen gejoint werden, das kann schon einen Moment dauern. Es kann aber genauso gut ein einziger Teil deines Querys sein, der für die lange Laufzeit verantwortlich ist, also nur einer der Joins, das Gruppieren, oder die Sortierung ganz am Schluss.
Oder es liegt wirklich einfach an einem absolut verhunzten Datenbankmodell, die Autoren von "High Performance MySQL" haben da in ihrem Buch mal eine nette Aussage geschrieben: Mit einer Query-Optimierung kannst du den Faktor x10 bis x100 durch eine Optimierung rausholen, Optimierungen mit dem Faktor x1000 oder noch viel mehr durch ein verbessertes Datenbankmodell sind dagegen keine Seltenheit.
Anmerkung: Dies ist nicht die wörtliche Aussage, sondern nur wie ich es im Kopf behalten habe, auch Zahlen können abweichen.

Wenn ausgeschlossen ist, dass ein besseres DB-Design möglich ist, dann schreibe den Query einmal neu und baue ihn langsam komplett neu auf und schaue welche Teile des Querys wieviel Zeit benötigen, dann findest du schnell den Schuldigen und kannst ihn beheben.

 

[Sebbo]

Vielleicht kannst du mal beschreiben, was du mit dem query bezweckst. Ich verstehe es leider nicht. Du hast ja nur 2 tabellen und musst aber 3 (inner) joins machen. Das klingt sehr merkwürdig. Vor allem ist mir unklar, was die pid ist. Ich würde auf product_id tippen, aber dann macht die WHERE-Bedinung für pp1.pid < pp2.pid keinen Sinn (soweit ich das verstehe). Im übrigen kannst du pp1.pid != pp2.pid weglassen - du schränkst ja noch auf kleiner als ein (das dürfte aber keine Auswirkungen haben).