Select Abfrage beschleunigen

C

Chimera16

Aktiver Benutzer
Beiträge
26
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!
 
Werbung:
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.
 
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
 
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!
 
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
 
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 ...
 
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 schrieb:
Wahrscheinlich ist nicht nur das Query schlecht sondern auch die Indexe falsch.
Zum Vergrößern anklicken....
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 :)
Code: 
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...
 
Ein Index sollte hier NIEMALS genutzt werden... Full Table Scan wird hier immer schneller sein. Weil man eben genau das braucht...
Zum Vergrößern anklicken....
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
 
BerndB schrieb:
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.
Zum Vergrößern anklicken....
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 :)
 
Werbung:
Distrilec schrieb:
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 :)
Zum Vergrößern anklicken....

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

Ähnliche Themen

C
Abfrage m:n tabellen geben 0 Datensätze zurück
Antworten
12
Aufrufe
762
Commu
C
L
Abfrage dauert zu lange
2 3
Antworten
34
Aufrufe
6K
long_forum
L
C
Select Abfrage um Einträge zu bekommen die immer den Wert 0 hatten
Antworten
4
Aufrufe
1K
Crafter
C
S
Got error 22 from storage engine
2
Antworten
20
Aufrufe
3K
santamo
S
N
WITH-Abfrage - wer erkennt Optimierungsbedarf?
Antworten
13
Aufrufe
4K
ny_unity
N
Zurück
Oben