MySQL Unabfrage

[mixmastertobsi]

Hallo, habe mal wieder ein Problem mit einer Datenbankabfrage.

Wir gehen von folgender Beispieldatenbankstruktur aus

auftragnr | artikel
---------------------
1000      | 23
1000      | 33
1000      | 34
1001      | 34
1002      | 23
1002      | 25
1003      | 24

Es sollen nun alle Zeilen mit Artikel "23" ausgegben werden und alle Zweilen, die diese Auftragnr haben.

Ausgabe

auftragnr | artikel
--------------------
1000      | 23
1000      | 33
1000      | 34
1002      | 23
1002      | 25

Ich habe bereits etwas mit JOIN experimentiert, jedoch ohne Erfolg.

[Vinzenz Mai]

Hallo,

Wir gehen von folgender Beispieldatenbankstruktur aus

auftragnr | artikel

1000      | 23
1000      | 33
1000      | 34
1001      | 34
1002      | 23
1002      | 25
1003      | 24

Es sollen nun alle Zeilen mit Artikel "23" ausgegben werden und alle Zweilen, die diese Auftragnr haben.

auftragnr | artikel

1000      | 23
1000      | 33
1000      | 34
1002      | 23
1002      | 25

d.h. gib mir alle Artikel aus den Aufträgen, die Artikel 23 enthalten. Typischer Fall für ein Subselect:

SELECT                          -- gib mir  
    t1.auftragnr,               -- Auftragsnummer  
    t1.artikel                  -- und Artikel  
FROM                            -- aus  
    tabelle t1                  -- meiner Tabelle.  
WHERE                           -- Dabei interessieren mich nur  
    auftragnr IN (              -- die Aufträge,  
        SELECT                  -- die in der Liste  
            t2.auftragnr        -- der Aufträge enthalten sind  
        FROM  
            tabelle t2  
        WHERE                   -- die  
            t2.artikel = 23     -- Artikel 23 enthalten.  
    )  

Beachte zusätzlich die Hinweise zur Optimierung von Subselects mit IN-Operator.

Freundliche Grüße

Vinzenz

[mixmastertobsi]

Hallo, genau so habe/hatte ichdie Abfrage bereits gestaltet gehabt. ABER - die Abfrage ist super langsam. Ich muss vielleicht dazu sagen, dass die Tabelle über 10000 Zeilen hat.

[MudGuard]

Hi,

Hallo, genau so habe/hatte ichdie Abfrage bereits gestaltet gehabt. ABER - die Abfrage ist super langsam.

Welche Indizes hast Du auf der Tabelle?

cu,
Andreas

[mixmastertobsi]

In dieser Tabelle eigentlich gar keine - außer eine ID als PRIMARY KEY

[ChrisB]

Hi,

In dieser Tabelle eigentlich gar keine - außer eine ID als PRIMARY KEY

Und dann wunderst du dich *ernsthaft* noch, dass das ganze langsam ist ...?
*seufz*

MfG ChrisB

[mixmastertobsi]

Ok - mit Indizes habe fast nie gearbeitet (außer bei Fulltext). Welchen Indizes sollte ich denn in meinem Fall verwenden?

[Vinzenz Mai]

Hallo,

Ok - mit Indizes habe fast nie gearbeitet (außer bei Fulltext). Welchen Indizes sollte ich denn in meinem Fall verwenden?

Lesetipp: MySQL-Handbuch, Abschnitt "How MySQL Uses Indexes"

wieviele verschiedene Spalten spielen bei dieser Abfrage, so wie hier besprochen eine Rolle? Ganze zwei. Das kann man sogar noch durchprobieren :-)
Ich hab's extra für Dich getan ...

Ich beziehe mich auf Deine Aussage hier im Thread.

In meinem Beispiel dauert die Abfrage 45 Sekunden.

da kannst Du Dich nicht beklagen :-)

Ich habe mir in meinem Testsystem (Intel Atom) eine Beispieltabelle mit den beiden angegebenen Spalten erstellt, ca. 10000 Datensätze mit 250 verschiedenen Artikeln, etwa 1800 Aufträgen und zwischen 1 und 10 Artikeln je Auftrag.

Hinweis: vor jeder einzelnen Abfrage habe ich per

RESET QUERY CACHE

den Query-Cache gelöscht.

Dauer der Abfrage, wenn keine Indexe vorhanden sind, 9 Minuten 48 Sekunden ...

a) Index nur für die Spalte artikel
Dauer der Abfrage ca. 5.7 Sekunden

b) Index nur für Spalte auftragnr
Dauer der Abfrage ca. 1.25 Sekunden

c) zwei Indexe
   Index für Spalte artikel,
   Index für Spalte auftragnr
Dauer der Abfrage ca. 1.25 Sekunden

d) ein kombinierter Index für die Spaltenreihenfolge (auftragnr, artikel)
Dauer der Abfrage ca. 0.30 Sekunden

e) ein kombinierter Index für die Spaltenreihenfolge (artikel, auftragnr)
Dauer der Abfrage ca. 0.45 Sekunden

f) zusätzlich zu d) Einzelindexe für die einzelnen Spalten
Dauer der Abfrage ca. 0.30 Sekunden

g) zusätzlich zu e) Einzelindexe für die einzelnen Spalten
Dauer der Abfrage ca. 0.45 Sekunden

Welchen Index bzw. welche Indexkombination würdest Du für *diese* Abfrage wählen? Lohnen sich _für diese Abfrage_ die Einzelindexe zusätzlich zu einem kombinierten Index?

Warum ist der kombinierte Index (auftragnr, artikel) *bei meinen Daten* lohnender als der kombinierte Index (artikel, auftragnr)?

Welche Hilfe kann Dir dabei EXPLAIN bieten?

Anmerkungen:
Die vorliegenden Daten können den Ausführungsplan beeinflussen.
Interessanterweise führte *bei meinen Daten* der Optimierungshinweis im Handbuch zu IN/=ANY-Subqueries zu kontraproduktiven Ergebnissen, sprich die Ausführungszeit erhöhte sich um ca. 5 bis 15%.

Freundliche Grüße

Vinzenz

[mixmastertobsi]

Mit dem Thema Index habe ich mich noch nie so sehr beschäftigt. Ich hatte lediglich bei auftragnr und artikelnummer einen Primary Index gesetzt gehabt.

Darf ich noch folgendes fragen - wann mach es sinn einen Index für jedes Feld zu setzen und wann sollte man den Index von zwei Spalten kombinieren?

[dedlfix]

Hi!

wann mach es sinn einen Index für jedes Feld zu setzen

Selten. Jeder Index bringt nicht nur Vorteile beim Suchen sondern kostet auch beim Datenändern. Indexe sind nur dann sinnvoll, wenn sie auch benutzt werden, also wenn Abfragen Werte in den indexierten Feldern suchen.

und wann sollte man den Index von zwei Spalten kombinieren?

Wenn du Abfragen hast, die anhand der beiden Spalten eine Auswahl treffen.

Allerdings solltest du immer mit EXPLAIN prüfen, ob die Indexe oder wenigstens einer auch verwendet werden. Zudem ist die Verwendung auch von der Anzahl der vorhandenen Daten abhängig. Wenn ein Index bereits die Datenmenge sehr weit eingedampft hat, lohnt sich die Verwendung eines zweiten Index nicht und er wird ignoriert. Welcher von beiden verwendet wird, hängt davon ab, wie MySQL denkt, welcher Index mehr einschränkend ist. Wenn jedoch noch genügend Daten übrigbleiben, kann auch ein zweiter Index herangezogen werden. Kombinationen bringen vor allem in solchen Szenarien was, wenn beide Indexe jeweils einzeln nicht weit genug einschränken.

Das sind alles nur Richtlinien. EXPLAIN sollte dein wichtigstes Werkzeug bei der Performance-Analyse werden. Das Ergebnis ist übrigens nicht feststehend sondern kann sich mit Datenmenge und abzufragenden Werten ändern. Wenn nach "a" in Feld1 gesucht wird, und das Ergebnis 10 Datensätze sind, wird der Index für Feld2 nicht verwendet. Allerdings kann die Suche nach "b" in Feld1 eine ganze Menge mehr Ergebnisse finden und dann lohnt sich der Feld2-Index wieder (falls im Feld2 auch wieder viele Datensätze zur befragten Bedingung zu finden sind).

Lo!

[mixmastertobsi]

Hallo,

soweit habe ich das verstanden. Meine Artikel-Daten werden sehr selten geändert. Es macht daher Sinn Indexes anzulegen.

Meine Frage ist aber, soll ich für jede Spalte einen Index anlegen oder kann ich diese kombinieren. Bzw. wo ist der Vorteil/Nachteil von kombinierten Indexes

Wir gehen mal von folgender beispiel Tabellstruktur aus

artikelnr | available | produktname
------------------------------------
1         | 1         | artikel 1
2         | 1         | artikel 2
3         | 0         | artikel 3

In diesem Fall - wie würde ich am besten den Index anlegen?!?
ADD INDEX INDEX ( artikelnr , available )
oder
ADD INDEX INDEX ( artikelnr  )
ADD INDEX INDEX ( available )

Die Tabelle wird wie folgt abgefragt
SELECT * FROM artikel WHERE available='1'

SELECT * FROM artikel WHERE available='1' AND artikelnr='2'

[Vinzenz Mai]

Hallo,

Meine Frage ist aber, soll ich für jede Spalte einen Index anlegen oder kann ich diese kombinieren. Bzw. wo ist der Vorteil/Nachteil von kombinierten Indexes

*seufz*
Wozu habe ich mir gestern für Dich soviel Arbeit gemacht?

Wir gehen mal von folgender beispiel Tabellstruktur aus

artikelnr | available | produktname

1         | 1         | artikel 1
2         | 1         | artikel 2
3         | 0         | artikel 3

In diesem Fall - wie würde ich am besten den Index anlegen?!?

Einfache Antwort: Bei sowenig Daten benötigt man überhaupt keinen Index.

ADD INDEX INDEX ( artikelnr , available )
oder
ADD INDEX INDEX ( artikelnr  )
ADD INDEX INDEX ( available )

Die Tabelle wird wie folgt abgefragt
SELECT * FROM artikel WHERE available='1'
SELECT * FROM artikel WHERE available='1' AND artikelnr='2'

ok, Du hast in Wirklichkeit deutlich mehr Daten in Deiner Tabelle.
Also mach das, was ich gestern gemacht habe: Teste es durch und nutze endlich selbst EXPLAIN, um herauszufinden, welcher Index bzw. welche Indexe genutzt werden.

Ein Index auf available wird wahrscheinlich höchst selten genutzt werden, weil er - wie dedlfix schon ausgeführt hat - die Menge der Datensätze nur in geringem Maße einschränken kann.

Überlege Dir bitte selbst, welches Kriterium bei Deiner zweiten Abfrage am stärksten einschränkt. Ist das so schwer?

Freundliche Grüße

Vinzenz

[mixmastertobsi]

OK - das Prinzip von Indexes habe ich verstanden.
Mir stellt sich allerdings die Frage, was der Vorteil ist Indexes zu kombinieren oder jede Spalte einzeleln zu indexieren - Oder kann pro Abfrage nur ein Index verwendet werden?

Des Weiteren habe ich einen Index erstellt, welche allerdings nicht verwendet wird. Warum?

Es sollen alle Artikel aus der Kategorie 10xxxx ausgeben werden.
... FROM artikel WHERE kategorie1 LIKE '10%'

id  select_type  table  type  possible_keys   key  key_len  ref  rows  Extra
1   SIMPLE       artikelALL   INDEX_KATEGORIE NULL NULL     NULL 761   Using where

[Vinzenz Mai]

Hallo,

Mir stellt sich allerdings die Frage, was der Vorteil ist Indexes zu kombinieren oder jede Spalte einzeleln zu indexieren

manchmal benötigt man das ohnehin. Wenn beispielsweise einem Auftrag ein Artikel nur genau ein einziges Mal zugeordnet werden darf, weil die Menge über eine Spalte Anzahl festgelegt wird, dann benötigst Du einen eindeutigen Index über die Spaltenkombination. Das ist in Zuordnungstabellen häufig zu sehen.

Außerdem kannst Du an meinem Beispielszenario sehen, dass der kombinierte Index die Abfrage nochmals um etwa den Faktor 4 beschleunigt - das ist nicht zu vernachlässigen.

• Oder kann pro Abfrage nur ein Index verwendet werden?

nein.

Des Weiteren habe ich einen Index erstellt, welche allerdings nicht verwendet wird. Warum?

Mögliche Gründe findest Du auf der von mir bereits verlinkten Handbuchseite, wie MySQL Indexe verwendet.

Es sollen alle Artikel aus der Kategorie 10xxxx ausgeben werden.
... FROM artikel WHERE kategorie1 LIKE '10%'

id  select_type  table  type  possible_keys   key  key_len  ref  rows  Extra
1   SIMPLE       artikelALL   INDEX_KATEGORIE NULL NULL     NULL 761   Using where

Ohne Kenntnis der vorhandenen Daten läßt sich das nicht so ohne weiteres sagen. Nochmals: ob ein Index sinnvoll genutzt werden kann, hängt von den vorhandenen Daten ab.

Freundliche Grüße

Vinzenz

[dedlfix]

Hi!

Meine Frage ist aber, soll ich für jede Spalte einen Index anlegen oder kann ich diese kombinieren. Bzw. wo ist der Vorteil/Nachteil von kombinierten Indexes

Wenn du meine Antwort aufmerksam gelesen hättest, müsste dir klar sein, dass man das nicht pauschal beantworten kann, du dir die tatsächliche Verwendung der Indexe anhand deiner Daten und typischer Abfragen von EXPLAIN nachweisen lassen musst, und das immer noch keine Garantie für die Verwendung des/der Indexe bei anderen Abfragewerten und/oder Datenmengen gibt.

Lo!

[Vinzenz Mai]

Hallo,

Hallo, genau so habe/hatte ichdie Abfrage bereits gestaltet gehabt.

aha. Sehr nett. Davon steht in Deiner Problembeschreibung nichts. Warum nicht?

ABER - die Abfrage ist super langsam. Ich muss vielleicht dazu sagen, dass die Tabelle über 10000 Zeilen hat.

Du hast also ein ganz anderes Problem, ein Performance-Problem. Das MySQL-Handbuch hat ein Optimierungskapitel.

Kopfschüttelnde Grüße

Vinzenz

[mixmastertobsi]

In meinem Beispiel dauert die Abfrage 45 Sekunden.