Select Abfrage beschleunigen

[Chimera16]

Hallo liebes Datenbankforum,

ich habe schon wieder Probleme mit der Performance meines SQL Servers...

Folgende Abfrage möchte ich gerne ausführen, sie dauert leider Ewigkeiten:

select t1.EAN, t1.Han, t2.EAN, t2.HAN
from produkte.produktliste_i t1, produkte.produktliste_a t2 WHERE
(t1.EAN = t2.EAN AND t1.HAN != t2.HAN AND t1.EAN != '') OR
(t1.HAN = t2.HAN AND t1.EAN != t2.EAN AND t1.HAN != '');

produktliste_a enthält ca. 36.000 Datensätze
produktliste_i enthält ca. 155.000 Datensätze

produktliste_a hat 2 Indexe, einen auf EAN und einen auf HAN
produktliste_i hat 2 Indexe, einen auf EAN und einen auf HAN

Was mache ich falsch? Sollte mein Index anders gesetzt sein? Ist meine Abfrage zu schlecht?

Danke für jede Antwort!

 

[Hubertus]

select t1.EAN, t1.Han, t2.EAN, t2.HAN
from produkte.produktliste_i t1, produkte.produktliste_a t2 WHERE
(t1.EAN = t2.EAN AND t1.HAN != t2.HAN AND t1.EAN != '')
UNION ALL
select t1.EAN, t1.Han, t2.EAN, t2.HAN
from produkte.produktliste_i t1, produkte.produktliste_a t2 WHERE
(t1.HAN = t2.HAN AND t1.EAN != t2.EAN AND t1.HAN != '');

sollte performanter sein.

 

[BerndB]

Na,

das ist es ja noch nicht.

Poste mal bitte die Ausgaben von

SELECT Version();

SHOW CREATE TABLE produktliste_a;

SHOW CREATE TABLE produktliste_i;

und

DESCRIBE select t1.EAN, t1.Han, t2.EAN, t2.HAN
from produkte.produktliste_i t1, produkte.produktliste_a t2 WHERE
(t1.EAN = t2.EAN AND t1.HAN != t2.HAN AND t1.EAN != '')
UNION ALL
select t1.EAN, t1.Han, t2.EAN, t2.HAN
from produkte.produktliste_i t1, produkte.produktliste_a t2 WHERE
(t1.HAN = t2.HAN AND t1.EAN != t2.EAN AND t1.HAN != '');

DESCRIBE select t1.EAN, t1.Han, t2.EAN, t2.HAN
from produkte.produktliste_i t1, produkte.produktliste_a t2 WHERE
(t1.EAN = t2.EAN AND t1.HAN != t2.HAN AND t1.EAN != '') OR
(t1.HAN = t2.HAN AND t1.EAN != t2.EAN AND t1.HAN != '');



Wahrscheinlich ist nicht nur das Query schlecht sondern auch die Indexe falsch.

Gruss

Bernd

 

[Chimera16]

Hallo Ihr beiden,

erst mal vielen Dank für eure Antworten!

@Hubertus : Es ist der Wahnsinn, meine alte Abfrage hat mit Limit 500 30 Sekunden gebraucht. Deine Abfrage braucht weniger als 2 Sekunden. Ich werde mich noch mal genauer damit befassen, warum deine Abfrage so viel schneller ist. Vielen Dank!

@BerndB Ich habe die Abfragen ausgeführt, hier die Ergebnisse:

Select Version:
Version()
5.6.25



produktliste_a

CREATE TABLE `produktliste_a` (
`EAN` varchar(20) NOT NULL,
`HAN` varchar(50) DEFAULT NULL,
`SKU` varchar(20) DEFAULT NULL,
`Bestand` int(20) DEFAULT NULL,
`Preis` decimal(20,2) DEFAULT NULL,
`Angebotspreis` decimal(20,2) DEFAULT NULL,
`Angeb_Preis_von` date DEFAULT NULL,
`Angeb_Preis_bis` date DEFAULT NULL,
`Beschreibung` text,
`Kategorie` mediumtext,
`Bild` varchar(500) DEFAULT NULL,
`Datenblatt` varchar(500) DEFAULT NULL,
`Hersteller` varchar(500) DEFAULT NULL,
`Name` varchar(500) DEFAULT NULL,
`Gewicht` decimal(20,2) DEFAULT NULL,
KEY `einzeln_EAN3` (`EAN`),
KEY `einzeln_HAN3` (`HAN`),
KEY `einzeln_SKU3` (`SKU`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


produktliste_i


CREATE TABLE `produktliste_i` (
`EAN` varchar(20) NOT NULL,
`HAN` varchar(50) DEFAULT NULL,
`SKU` varchar(20) DEFAULT NULL,
`Bestand` int(20) DEFAULT NULL,
`Preis` decimal(20,2) DEFAULT NULL,
`Angebotspreis` decimal(20,2) DEFAULT NULL,
`Angeb_Preis_von` date DEFAULT NULL,
`Angeb_Preis_bis` date DEFAULT NULL,
`Beschreibung` text,
`Kategorie` mediumtext,
`Bild` varchar(500) DEFAULT NULL,
`Datenblatt` varchar(500) DEFAULT NULL,
`Hersteller` varchar(500) DEFAULT NULL,
`Name` varchar(500) DEFAULT NULL,
`Gewicht` decimal(20,2) DEFAULT NULL,
KEY `einzeln_EAN` (`EAN`),
KEY `einzeln_HAN` (`HAN`),
KEY `einzeln_SKU` (`SKU`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8



Das erste Decribe:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL einzeln_EAN3 NULL NULL NULL 35672 Using where
1 PRIMARY t1 ref einzeln_EAN einzeln_EAN 62 produkte.t2.EAN 1 Using where
2 UNION t2 ALL einzeln_HAN3 NULL NULL NULL 35672 Using where
2 UNION t1 ref einzeln_HAN einzeln_HAN 153 produkte.t2.HAN 1 Using where
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using temporary


Das zweite Descibe:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL einzeln_EAN,einzeln_HAN NULL NULL NULL 170688 Using where
1 SIMPLE t2 ALL einzeln_EAN3,einzeln_HAN3 NULL NULL NULL 35672 Using where; Using join buffer (Block Nested Loop)

Kannst du damit was anfangen?

Vielen Dank nochmal für eure Mühe!

 

[BerndB]

Was direkt zu sehen ist:

Wie erwartet werden keine Indexe benutzt

Kannst mich ja mal kurz anrufen unter

02163 / 5719653

Damit ich genau weiss was du da machen willst. Dann kann ich dir die Lösung
dann posten.

Gruss

Bernd

 

[akretschmer]

Ist das noch aktuell?

Was mir so auffällt ist z.B., daß eine EAN IIRC 13 stellen lang ist, und 'richtige' Datenbanken sogar eigene Datentypen dafür haben, die die Prüfsumme einer EAN checken. Mal so als Hinweis. Falls das Problem noch aktuell ist würde ich auch noch mal tiefer mich damit beschäftigen ...

 

[Chimera16]

Hallo ihr beiden,

entschuldigt meine verspätete Antwort, ich hatte die letzten Tage zu viel Streß auf der Arbeit...

@BerndB Vielen Dank! Die Abfrage ist jetzt zumindest "aushaltbar" schnell.. Ich würde dein Angebot aber trotzdem gerne annehmen.

@akretschmer Danke dir Akretschmer! Ich werde mal gucken, ob ich etwas passendes im Internet finde... Verhältnismäßig ist meine Datenbank mit ca. 250.000 Datensätzen noch klein... Aber ich merke schon, dass die Performance mit jedem Datensatz nachlässt...

 

[BerndB]

Kannst du gerne heute Abend machen.

Einfach anrufen, bin ab ca. 19:00 Uhr erreichbat.

Schönen Tag

Bernd

 

[rammi22]

...Folgende Abfrage möchte ich gerne ausführen, sie dauert leider Ewigkeiten:...
Sollte mein Index anders gesetzt sein? Ist meine Abfrage zu schlecht?...

EXPLAIN befragen (MySql-Version beachten)

 

[Distrilec]

Wahrscheinlich ist nicht nur das Query schlecht sondern auch die Indexe falsch.

Ein Index sollte hier NIEMALS genutzt werden... Full Table Scan wird hier immer schneller sein. Weil man eben genau das braucht...

@Hubertus Query kann man noch um ein "Not Null" reduzieren... Aber ansonsten ist das so ziemlich das optimum das man rausholen kann

select t1.EAN, t1.Han, t2.EAN, t2.HAN
from produkte.produktliste_i t1, produkte.produktliste_a t2 WHERE
t1.EAN = t2.EAN AND t1.HAN != t2.HAN
UNION ALL
select t1.EAN, t1.Han, t2.EAN, t2.HAN
from produkte.produktliste_i t1, produkte.produktliste_a t2 WHERE
t1.HAN = t2.HAN AND t1.EAN != t2.EAN AND t1.HAN != '';

Evtl. kann man hier noch mit zwei Joins statt dem Union spielen, um den zweiten Full Table Scan der "produktstückliste_i" zu sparen... Das wäre aber ziemliche haarspalterei...

Ein anderer Ansatz: Solltest du nur die EAN und HAN einer der beiden Tabellen brauchen könntest du mit Minus arbeiten...Wäre dann aber eine ziemliche RAM-Auslastung, sollte sich die DB entscheiden ne Hash-Table aus der "produkstücklste_a" zu machen...

 

[BerndB]

Ein Index sollte hier NIEMALS genutzt werden... Full Table Scan wird hier immer schneller sein. Weil man eben genau das braucht...

Das solltest du aber mal näher erklären, das es schneller ist wenn für jede Zeile in Tabelle 1 einen FULL TABLE SCAN in Tabelle 2 machst.

Gruss

Bernd

 

[Distrilec]

Das solltest du aber mal näher erklären, das es schneller ist wenn für jede Zeile in Tabelle 1 einen FULL TABLE SCAN in Tabelle 2 machst.

Du hast wohl keine Ahnung was deine Datenbank eigentlich macht wenn sie zwei Tabellen joint?
Nested Loop Operation, Hash Join, Sort Merge Join...
Was du beschrieben hast ist ein Nested Loop und wird nur gemacht wenn die Anzahl an Datensätze in der Basis-Tabelle vergleichsweise gering ist...
Für die Datenmengen wie vom TO beschrieben wird es wohl eher ein Hash-Join sein. Die kleinere von beiden Tabellen wird gehasht (in-mem) und dann wird gegen die große Tabelle geprobed... Was übrig bleibt ist dann dein Resultset...

Tiefer werde ich da nicht drauf eingehen, selbst MySQL sollte dafür ne Doku haben

 

[BerndB]

Du hast wohl keine Ahnung was deine Datenbank eigentlich macht wenn sie zwei Tabellen joint?
Nested Loop Operation, Hash Join, Sort Merge Join...
Was du beschrieben hast ist ein Nested Loop und wird nur gemacht wenn die Anzahl an Datensätze in der Basis-Tabelle vergleichsweise gering ist...
Für die Datenmengen wie vom TO beschrieben wird es wohl eher ein Hash-Join sein. Die kleinere von beiden Tabellen wird gehasht (in-mem) und dann wird gegen die große Tabelle geprobed... Was übrig bleibt ist dann dein Resultset...

Tiefer werde ich da nicht drauf eingehen, selbst MySQL sollte dafür ne Doku haben

joooo, genau so hab ich mir das gedacht. Du hast mir das Wochenende versüsst, Danke