6
MySQL - Einfache Select -Join Abfrage dauert ewig
Beim verbinden mehrerer Tabellen in einer Select-Abfrage wird das Ergebnis nach knapp einer Minute geliefert, der slow_query_counter zählt dabei natürlich hoch.
Hi @all,
wie ich bereits beschrieben habe, verwende ich ein einfache Abfrage welche Tabellen verbinden soll. Hier mal die Strucktur.
Index <- Primary; Autoimcrement
ImportId <- Indexkey = Index über Spalte
Artikelgruppe
Vaterartikelgruppe
Basisartikelgruppe
Bild1
Bild2
Bild3
usw
Index <- Primary; Autoincrement
ImportId <- Unique
a
b
c
d
usw
Index <- Primary, Autoincrement
ImportId <- Unique
Cr
C0r
Gewicht
usw.
In der Tabelle Artikel erfasse ich lediglich Artikelgrunddaten wie Bilder zum Artikel, Artikelgruppen etc. Jede Artikelgruppe enthält einen bestimmten Artikeltyp wie zum Beispiel "Kettenrad" der sich von anderen Artikeln unterscheidet, daher auch die Tabellen 101010_Abmessungen (für die Abmessungen des Artikels) und 101010_TechnischeDaten (für Infos wie Bruchlast, Tragzahlen, Gewicht etc). Die Zahlen im Tabellenkopf stehen also immer für die Basisartikelgruppe des jeweiligen Artikels.
Über eine Abfrage Verbinde ich die Tabellen (je nach dem was für ein Artikeltyp ich benötige) und generiere mir eine Tabelle. Die Abfrage funktioniert bis dahin einwandfrei und auch sehr zügig.
SELECT *
FROM Artikel AS t1
INNER JOIN 101010_Abmessungen AS t2 ON ( t1.ImportID = t2.ImportID )
INNER JOIN 101010_TechnischeDaten AS t3 ON ( t1.ImportID = t3.ImportID )
WHERE t1.ImportId like 'IRGENDWAS'
Wie in der Abfrage zu sehen ist, verbinde ich meine Tabellen nicht über den Primary_Key "Index" sondern über die Spalte ImportId, weil diese Quasi "Eineindeutig" ist. Mit anderen Worten, meine Tabelle Artikel enthält 24.000 Datensätze (Tendenz steigend weil ich noch nicht alle Erfasst habe), während je nach Artikel die Tabelle XXX_Abmessungen nur 100 oder 2 Tausend oder X-Datensätze enthält.
Nun zum Problem:
Da ich meine Tabellen nicht über den Primary_Key verbinde sondern über die ImportId möchte ich in der Tabelle "Artikel" über die Spalte "ImportId" den Indextyp UNIQUE anlegen, damit die Tabelle nicht bei jeder Abfrage squentiel gelesen werden muss. Sobald ich aber für diese Spalte den Typ UNIQUE festlege dauert die Abfrage wie sie oben geschrieben ist auf einmal fast eine Minute.
Analyse der Tabellen sowie optimieren beschleunigen diese Abfrage nicht. Ich erhalte lediglich die Info, dass die Tabellen in Ordnung sind.
Meine Frage an euch: Warum zickt der Server so rum wenn ich in der Tabelle "Artikel" UNIQUE für die ImportId festlege, die anderen Tabellen (Abmessungen, TechnischeDaten etc) haben doch auch den UNIQUE über die Spalte ImportId. Wenn ich den UNIQUE über der Spalte Artikel.ImportId "drop" und einen einfachen Index anlege, funktioniert die Abfrage wieder einwandfrei. Ich bin etwas verwundert darüber und dankbar für jeden Tipp.
Ich entschuldige mich im Voraus schon mal für diesen Roman und danke für eure Hilfe.
wie ich bereits beschrieben habe, verwende ich ein einfache Abfrage welche Tabellen verbinden soll. Hier mal die Strucktur.
| Artikel |
ImportId <- Indexkey = Index über Spalte
Artikelgruppe
Vaterartikelgruppe
Basisartikelgruppe
Bild1
Bild2
Bild3
usw
| 101010_Abmessungen |
ImportId <- Unique
a
b
c
d
usw
| 101010_TechnischeDaten |
ImportId <- Unique
Cr
C0r
Gewicht
usw.
In der Tabelle Artikel erfasse ich lediglich Artikelgrunddaten wie Bilder zum Artikel, Artikelgruppen etc. Jede Artikelgruppe enthält einen bestimmten Artikeltyp wie zum Beispiel "Kettenrad" der sich von anderen Artikeln unterscheidet, daher auch die Tabellen 101010_Abmessungen (für die Abmessungen des Artikels) und 101010_TechnischeDaten (für Infos wie Bruchlast, Tragzahlen, Gewicht etc). Die Zahlen im Tabellenkopf stehen also immer für die Basisartikelgruppe des jeweiligen Artikels.
Über eine Abfrage Verbinde ich die Tabellen (je nach dem was für ein Artikeltyp ich benötige) und generiere mir eine Tabelle. Die Abfrage funktioniert bis dahin einwandfrei und auch sehr zügig.
SELECT *
FROM Artikel AS t1
INNER JOIN 101010_Abmessungen AS t2 ON ( t1.ImportID = t2.ImportID )
INNER JOIN 101010_TechnischeDaten AS t3 ON ( t1.ImportID = t3.ImportID )
WHERE t1.ImportId like 'IRGENDWAS'
Wie in der Abfrage zu sehen ist, verbinde ich meine Tabellen nicht über den Primary_Key "Index" sondern über die Spalte ImportId, weil diese Quasi "Eineindeutig" ist. Mit anderen Worten, meine Tabelle Artikel enthält 24.000 Datensätze (Tendenz steigend weil ich noch nicht alle Erfasst habe), während je nach Artikel die Tabelle XXX_Abmessungen nur 100 oder 2 Tausend oder X-Datensätze enthält.
Nun zum Problem:
Da ich meine Tabellen nicht über den Primary_Key verbinde sondern über die ImportId möchte ich in der Tabelle "Artikel" über die Spalte "ImportId" den Indextyp UNIQUE anlegen, damit die Tabelle nicht bei jeder Abfrage squentiel gelesen werden muss. Sobald ich aber für diese Spalte den Typ UNIQUE festlege dauert die Abfrage wie sie oben geschrieben ist auf einmal fast eine Minute.
Analyse der Tabellen sowie optimieren beschleunigen diese Abfrage nicht. Ich erhalte lediglich die Info, dass die Tabellen in Ordnung sind.
Meine Frage an euch: Warum zickt der Server so rum wenn ich in der Tabelle "Artikel" UNIQUE für die ImportId festlege, die anderen Tabellen (Abmessungen, TechnischeDaten etc) haben doch auch den UNIQUE über die Spalte ImportId. Wenn ich den UNIQUE über der Spalte Artikel.ImportId "drop" und einen einfachen Index anlege, funktioniert die Abfrage wieder einwandfrei. Ich bin etwas verwundert darüber und dankbar für jeden Tipp.
Ich entschuldige mich im Voraus schon mal für diesen Roman und danke für eure Hilfe.
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 188495
Url: https://administrator.de/contentid/188495
Printed on: April 24, 2024 at 20:04 o'clock
6 Comments
Latest comment
Datantyp bzw Länge von 'ImportId' ?
Frage:
Wenn 'ImportId' schon UNIQUE ist - wozu dann noch 'Index' als PK mit AI definieren ? ... 'ImportId' könnte doch dann gleich als PK genommen werden !? ... wenn dem so ist, dann könnten ja auch die Tabellen '101010_Abmessungen' und '101010_TechnischeDaten' zusammen gefasst werden ?
EDIT
Frage:
Geht die Abfrage evtl deutlich schneller wenn Du nur die Keys SELECTierst ?
Frage:
Wenn 'ImportId' schon UNIQUE ist - wozu dann noch 'Index' als PK mit AI definieren ? ... 'ImportId' könnte doch dann gleich als PK genommen werden !? ... wenn dem so ist, dann könnten ja auch die Tabellen '101010_Abmessungen' und '101010_TechnischeDaten' zusammen gefasst werden ?
EDIT
Frage:
Geht die Abfrage evtl deutlich schneller wenn Du nur die Keys SELECTierst ?
@nxclass
stimmt eigentlich, dass werde ich gleich mal so umbauen...
An sich wäre das kein Problem. Es geht mir persönlich aber um eine "Ordnung" weil ich die Tabelle "TechnischeDaten" immer erst dann brauche, wenn ein Kunde wirklich das technische Datenblatt zu einem Produkt benötigt. Damit verfolge ich das Ziel den DB-Traffic so schmal wie möglich zu halten, weil ich alle Tabellen erst in der "ich sage mal salop... dritten Ebene benötige". Die Daten also schon mit abzufragen obwohl sie nicht gebraucht werden ist aus meiner Sicht nicht so Ressourcenschonend, gerade bei der Produktvielfalt.
Aber ich danke dir für deinen Tipp und werd das gleich mal umsetzen. Meld mich gleich wieder
Edit
Datentyp von ImportId ist varChar(50)
Wenn 'ImportId' schon UNIQUE ist - wozu dann noch 'Index' als PK mit AI definieren ?
stimmt eigentlich, dass werde ich gleich mal so umbauen...
wenn dem so ist, dann könnten ja auch die Tabellen '101010_Abmessungen' und '101010_TechnischeDaten' zusammen gefasst werden ?
An sich wäre das kein Problem. Es geht mir persönlich aber um eine "Ordnung" weil ich die Tabelle "TechnischeDaten" immer erst dann brauche, wenn ein Kunde wirklich das technische Datenblatt zu einem Produkt benötigt. Damit verfolge ich das Ziel den DB-Traffic so schmal wie möglich zu halten, weil ich alle Tabellen erst in der "ich sage mal salop... dritten Ebene benötige". Die Daten also schon mit abzufragen obwohl sie nicht gebraucht werden ist aus meiner Sicht nicht so Ressourcenschonend, gerade bei der Produktvielfalt.
Aber ich danke dir für deinen Tipp und werd das gleich mal umsetzen. Meld mich gleich wieder
Edit
Datentyp von ImportId ist varChar(50)
Datentyp von ImportId ist varChar(50)
evtl ist das einfach zu viel - es gibt die Möglichkeit eine weitere Tabelle anzulegen:import_keys mit den Feldern id (INT PK + AI) und importid (varchar50 UK)
Dann könntest Du mit einem simplen INT statt mit einem CHAR(50) die Tabellen verbinden und hast trotzdem die Möglichkeit noch auf die "orginal" importId zurück zu greifen.
Danke für deine Tipps. Ich habe mal die Lösung probiert welche du vorgeschlagen hast, allerdings mit geringem erfolg.
Die Abfrage bringt das Ergbenis nach 14.0462 sek. Hin und her probiert und eigentlich aus Spaß einfach mal den Ausgangspunkt meiner Abfrage geändert.
Alte Abfrage:
SELECT *
FROM Artikel AS t1
INNER JOIN 301011_Abmessungen AS t2 ON ( t1.ImportID = t2.ImportID )
INNER JOIN 301011_TechnischeDaten AS t3 ON ( t1.ImportID = t3.ImportID )
INNER JOIN 301011_Zubehoer AS t4 ON ( t1.ImportID = t4.ImportID )
WHERE t1.ImportId like '%KRS32%'
Ergebnis: 14.0462 sek (94 Datensätze)
Neue Abfrage:
SELECT *
FROM 301011_Abmessungen AS t1
INNER JOIN Artikel AS t2 ON ( t1.ImportID = t2.ImportID )
INNER JOIN 301011_TechnischeDaten AS t3 ON ( t1.ImportID = t3.ImportID )
INNER JOIN 301011_Zubehoer AS t4 ON ( t1.ImportID = t4.ImportID )
WHERE t1.ImportId like '%KRS32%'
Ergebnis: 0.2570 sek (94 Datensätze)
Ich verstehe die Welt gerade nicht mehr. (Die Key's habe ich so angelegt wie du es vorgeschlagen hast. Der AI bringt es ja wirklich nicht an dieser Stelle).
Heißt also ich könnte in jeder Tabelle auf die Spalte ImportId verzichten und dafür aber eine ImportKey-Spalte hinzufügen, welche am Ende aus der Tabelle Import_keys herangezogen wird und in der Abfrage auf diese auch beziehen?
Die Abfrage bringt das Ergbenis nach 14.0462 sek. Hin und her probiert und eigentlich aus Spaß einfach mal den Ausgangspunkt meiner Abfrage geändert.
Alte Abfrage:
SELECT *
FROM Artikel AS t1
INNER JOIN 301011_Abmessungen AS t2 ON ( t1.ImportID = t2.ImportID )
INNER JOIN 301011_TechnischeDaten AS t3 ON ( t1.ImportID = t3.ImportID )
INNER JOIN 301011_Zubehoer AS t4 ON ( t1.ImportID = t4.ImportID )
WHERE t1.ImportId like '%KRS32%'
Ergebnis: 14.0462 sek (94 Datensätze)
Neue Abfrage:
SELECT *
FROM 301011_Abmessungen AS t1
INNER JOIN Artikel AS t2 ON ( t1.ImportID = t2.ImportID )
INNER JOIN 301011_TechnischeDaten AS t3 ON ( t1.ImportID = t3.ImportID )
INNER JOIN 301011_Zubehoer AS t4 ON ( t1.ImportID = t4.ImportID )
WHERE t1.ImportId like '%KRS32%'
Ergebnis: 0.2570 sek (94 Datensätze)
Ich verstehe die Welt gerade nicht mehr. (Die Key's habe ich so angelegt wie du es vorgeschlagen hast. Der AI bringt es ja wirklich nicht an dieser Stelle).
import_keys mit den Feldern id (INT PK + AI) und importid (varchar50 UK)
Heißt also ich könnte in jeder Tabelle auf die Spalte ImportId verzichten und dafür aber eine ImportKey-Spalte hinzufügen, welche am Ende aus der Tabelle Import_keys herangezogen wird und in der Abfrage auf diese auch beziehen?
Ich verstehe die Welt gerade nicht mehr.
bedenke bei deinen Tests: die DB ist nicht "dumm" - sie optimiert die Abfragen und erkennt evtl. das es sich um die selbe Abfrage handelt und gibt Dir das zwischen gespeicherte Ergebnis zurück.Verwende daher möglichst eindeutige und unterschiedliche WHERE Bedingungen
Heißt also ich könnte in jeder Tabelle auf die Spalte ImportId verzichten
... das hat nur den geringen Effekt, dass die Datenmenge der Indexe kleiner wird, und so die suche der Schlüssel etwas schneller. ABER in der Tabelle 'Import_keys' muss dann aber zusätzlich noch nach der 'importId' gesucht werden, was wieder zeit kostet.Führe mal deine Tests nur mit den Schlüsseln durch:
SELECT t1.ImportID, t2.ImportID, t3.ImportID, t4.ImportID
FROM Artikel AS t1
INNER JOIN 301011_Abmessungen AS t2 ON ( t1.ImportID = t2.ImportID )
INNER JOIN 301011_TechnischeDaten AS t3 ON ( t1.ImportID = t3.ImportID )
INNER JOIN 301011_Zubehoer AS t4 ON ( t1.ImportID = t4.ImportID )
WHERE t1.ImportId like '%KRS32%' EDIT:
Es geht mir persönlich aber um eine "Ordnung" weil ich die Tabelle ...
.. die Daten in einer Tabelle sind sicher "näher" beieinander auf der Festplatte als wenn diese in mehreren Tabellen verteilt sind - dies ist dann natürlich ausschlaggebend wenn die DB diese Daten laden muss.
HI nxclass,
danke für dein Hilfe, du bist ein GOTT! Die Lösungsvariante, welche du als erstes vorgeschlagen hast, habe ich gestern Abend einfach noch mal umgesetzt.
Ich habe jetzt eine zusätzliche Tabelle in welcher ich, so wie du vorgeschlagen hast, der ImportId einen import_key verpasse. Bei den anderen Tabellen (Artikel, Abmessungen, TechnischeDaten usw) habe ich den Index unbenannt in import_key, AI für die Spalte gedropt und über eine Abfrage die ID aus der Tabelle import_keys herangeholt.
Folgende Abfrage habe ich dazu verwendet (Bsp).
UPDATE Artikel SET `import_key` = (SELECT Max(import_keys.id) FROM import_keys WHERE import_keys.ImportId = Artikel.ImportId) ... usw.
Nach dem ich die import_keys in alles anderen Tabellen aktualisiert habe, habe ich nun der import_key -Spalte den PK >und der ImportId den Index verpasst.
Tabellen Analysiert und optimiert....
Nach Optimierung wieder meine Abfrage
SELECT *
FROM Artikel AS t1
INNER JOIN 301010_Abmessungen AS t2 ON ( t1.import_key = t2.import_key )
INNER JOIN 301010_TechnischeDaten AS t3 ON ( t1.import_key = t3.import_key )
INNER JOIN 301010_Zubehoer AS t4 ON ( t1.import_key = t4.import_key )
Ergebnis: 1,513 insgesamt, die Abfrage dauerte 0.0472 sek
Das Coole ist natürlich jetzt, dass ich meine Abfragen nun mit Key's erstellen kann und diese am Ende auch im Log nicht mehr angemeckert werden. Warum ich da selbst nicht darauf gekommen bin? Keine Ahnung wo ich mit meinen Gedanken war.
Resultat also: GEIL. So passt das! Sag bescheid wenn du mal in meiner Nähe bist, dann spendiere ich ein Bier!
Vielen Dank noch mal,
Knut
danke für dein Hilfe, du bist ein GOTT! Die Lösungsvariante, welche du als erstes vorgeschlagen hast, habe ich gestern Abend einfach noch mal umgesetzt.
Ich habe jetzt eine zusätzliche Tabelle in welcher ich, so wie du vorgeschlagen hast, der ImportId einen import_key verpasse. Bei den anderen Tabellen (Artikel, Abmessungen, TechnischeDaten usw) habe ich den Index unbenannt in import_key, AI für die Spalte gedropt und über eine Abfrage die ID aus der Tabelle import_keys herangeholt.
Folgende Abfrage habe ich dazu verwendet (Bsp).
UPDATE Artikel SET `import_key` = (SELECT Max(import_keys.id) FROM import_keys WHERE import_keys.ImportId = Artikel.ImportId) ... usw.
Nach dem ich die import_keys in alles anderen Tabellen aktualisiert habe, habe ich nun der import_key -Spalte den PK >und der ImportId den Index verpasst.
Tabellen Analysiert und optimiert....
Nach Optimierung wieder meine Abfrage
SELECT *
FROM Artikel AS t1
INNER JOIN 301010_Abmessungen AS t2 ON ( t1.import_key = t2.import_key )
INNER JOIN 301010_TechnischeDaten AS t3 ON ( t1.import_key = t3.import_key )
INNER JOIN 301010_Zubehoer AS t4 ON ( t1.import_key = t4.import_key )
Ergebnis: 1,513 insgesamt, die Abfrage dauerte 0.0472 sek
Das Coole ist natürlich jetzt, dass ich meine Abfragen nun mit Key's erstellen kann und diese am Ende auch im Log nicht mehr angemeckert werden. Warum ich da selbst nicht darauf gekommen bin? Keine Ahnung wo ich mit meinen Gedanken war.
Resultat also: GEIL. So passt das! Sag bescheid wenn du mal in meiner Nähe bist, dann spendiere ich ein Bier!
Vielen Dank noch mal,
Knut
