Doppel-Select-Anweisung zu langsam

[Morphie]

So, Enterprise Server heruntergeladen und installiert.

Seltsamerweise ist der sogar noch langsamer als der Community-Server.
Ich habe den SQL-Dump also ganz frisch auf dem Enterprise-Server eingelesen. Keine Daten(banken) übernommen!

Vorsichtshalber habe ich die Abfrage gleich 3x hintereinander durchgeführt:

Zitat:

SELECT SUM(POSITIONEN.MENGE) FROM POSITIONEN INNER JOIN BELEGE ON (POSITIONEN.BELEGNR = BELEGE.BELEGNR) WHERE POSITIONEN.ARTIKELNR = '1090213000' AND BELEGE.BELEGART = 'A' ;
/* 0 rows affected, 1 rows found. Duration for 1 query: 9,048 sec. */

SELECT SUM(POSITIONEN.MENGE) FROM POSITIONEN INNER JOIN BELEGE ON (POSITIONEN.BELEGNR = BELEGE.BELEGNR) WHERE POSITIONEN.ARTIKELNR = '1090213000' AND BELEGE.BELEGART = 'A' ;
/* 0 rows affected, 1 rows found. Duration for 1 query: 15,538 sec. */

SELECT SUM(POSITIONEN.MENGE) FROM POSITIONEN INNER JOIN BELEGE ON (POSITIONEN.BELEGNR = BELEGE.BELEGNR) WHERE POSITIONEN.ARTIKELNR = '1090213000' AND BELEGE.BELEGART = 'A' ;
/* 0 rows affected, 1 rows found. Duration for 1 query: 11,841 sec. */

Ich habe mir mal den Ausführungsplan anzeigen lassen
MySQL Enterprise:

Code:

id   select_type   table        type     possible_keys               key         key_len    ref                             rows     filtered   Extra
1   SIMPLE        POSITIONEN   ref      PRIMARY,artikelnr,belegnr   artikelnr   53        const                           16160   100.00    Using where
1   SIMPLE        BELEGE       eq_ref   PRIMARY,belegart            PRIMARY     52        POSITIONEN.belegnr              1       100.00    Using where

MariaDB:

Code:

id   select_type   table        type     possible_keys               key         key_len    ref                             rows     filtered   Extra
1   SIMPLE        POSITIONEN   ref      PRIMARY,artikelnr,belegnr   artikelnr   53        const                           16160   100.00    Using index condition
1   SIMPLE        BELEGE       eq_ref   PRIMARY,belegart            PRIMARY     52        POSITIONEN.belegnr              1       100.00    Using where

MariaDB macht also irgendwas anders als MySQL.
Kann man dieses Verhalten MySQL beibringen? ("USE INDEX" oder ähnliches?)

[Codehunter]

Das geht ziemlich in die Interna, da müßte man wahrscheinlich mal direkt bei MariaDB anfragen. (Bei MySQL solltest du damit lieber nicht landen denke ich *gg*)

[Codehunter]

Nachtrag (grade erst gesehen): http://www.heise.de/newsticker/meldu...6-1796834.html

[Morphie]

Ach, MariaDB läuft ja wie gesagt extrem schnell. Es gibt dort keinerlei Probleme, daher lassen wir es jetzt so wie es ist.
Es wundert mich aber sehr, dass so eine simple SQL-Abfrage so einen großen Unterschied wirft. Vor allem bei "Der populärsten Open-Source-Datenbank der Welt"

Und im Zusammenhang mit der eigenen Client Library vom MariaDB (libmysql.dll), die unter LGPL lizensiert ist, fällt mir auch kein Grund ein, warum man sich noch den MySQL-Server von Oracle ins Haus holen sollte...

[generic]

Welche Datentypen werden hier benutzt?
In den Beispielen sehe ich immer nur Strings.

Datum, Artikelnummer sind theoretisch schöne Zahlen, wo Computer prima Sortieren und Vergleichen können.
Strings sind für Sortieren und Vergleichen eher schlecht.

[jobo]

Zitat von Morphie:

So, Enterprise Server heruntergeladen und installiert.

Seltsamerweise ist der sogar noch langsamer als der Community-Server.
Ich habe den SQL-Dump also ganz frisch auf dem Enterprise-Server eingelesen. Keine Daten(banken) übernommen!
MariaDB macht also irgendwas anders als MySQL.
Kann man dieses Verhalten MySQL beibringen? ("USE INDEX" oder ähnliches?)

Index hints
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

Dein Vergleich ist glaub ich etwas unfair. Eine frische Datenbank hat idR. noch keine Statistiken aufgebaut und die Optimizer Entscheidungen können die Selektivität eines Index nicht berücksichtigen. Hab kein Plan, was man da bei mysql alles anwerfen muss, aber es sollte sich bei größeren Datenmengen schon lohnen, die Statistiken zu fahren.

[Morphie]

Zitat von generic:

Welche Datentypen werden hier benutzt?

In meinem Fall sind es VARCHARs. Artikelnummern dürfen Alphanumerisch sein, genau wie Belegnr und Belegart.
Ein Datum berücksichtige ich hier nicht.

Zitat von jobo:

Dein Vergleich ist glaub ich etwas unfair. Eine frische Datenbank hat idR. noch keine Statistiken aufgebaut und die Optimizer Entscheidungen können die Selektivität eines Index nicht berücksichtigen. Hab kein Plan, was man da bei mysql alles anwerfen muss, aber es sollte sich bei größeren Datenmengen schon lohnen, die Statistiken zu fahren.

Alle Datenbanken (MariaDB, MySQL Community, MySQL Enterprise) sind frisch. Vor jedem Test lade ich ein Dump-File (1,2 GB) frisch in die Datenbanken.
Bevor wir auf MariaDB umgestellt haben, lief die DB mehrere Jahre unter MySQL. Dort waren die Abfragen aber genauso langsam.

Zitat von jobo:

Index hints
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

Damit habe ich schon etwas rumgespielt. Bekomme aber nie das Ergebnis von MariaDB hin...



Naja, nu ists aber auch egal, ist ja nicht mein Thema hier

[Blup]

Zitat von jobo:

Das ist nun das "vermutlich" 3. verschlimmbesserte Statement, deshalb bitte noch mal meinen vorigen Beitrag berücksichtigen.

"Vermutlich" deshalb, weil ein paar Detailangaben des TE fehlen. Sein ursprüngliches SQL ist hier sehr wahrscheinlich das einzig formal richtige, das implizit (durch das IN) ein Distinct auf die Menge macht. Alle anderen Statements machen das nicht und würden bei einer Grundmenge von 500T Sätzen solange daten permutieren, bis der Tablespace voll ist.

Die Bedingung ist in der View berücksichtigt.

Zitat von jobo:

Und Views bringen hier eher keinen Geschwindigkeitsvorteil.

In diesem Fall schon, sie zwingt den Server die richtigen Dinge in der richtigen Reihenfolge zu machen und nicht den Speicher voll zu müllen. Allerdings sind die beiden Indexe notwendig, sonst bringt alles nichts.

[jobo]

Zitat von Blup:

Die Bedingung ist in der View berücksichtigt.

Welche Bedingung meinst du? Die Datumseinschränkung? Klar, ist ja die einzige Bedingung. Das meine ich aber nicht.
Alle SQL Statements außer dem des TE im ersten Post permutieren mit ziemlicher Sicherheit die Daten!
Also aus 10 Datensätzen werden z.B. 25, aus 200 werden 2000 usw.
Aus 5000000 wie beim TE werden vermutlich sehr sehr viele..

Zitat von Blup:

Zitat von jobo:

Und Views bringen hier eher keinen Geschwindigkeitsvorteil.

In diesem Fall schon, sie zwingt den Server die richtigen Dinge in der richtigen Reihenfolge zu machen und nicht den Speicher voll zu müllen. Allerdings sind die beiden Indexe notwendig, sonst bringt alles nichts.

Also ich weiß nicht, wie der Optimizer von mySQL mit einem View umgeht. Aber man kann ja ganz leicht die Ausführungspläne vergleichen mit/ihne View. Dann spart man sich die Spekulation.

[generic]

Du kannst die Artikelnummern Hashen und in einer zusätzlichen Spalte ablegen.
Diese dann Indizien und für den Join als erstes! Argument nutzen.

Code:

SELECT SUM(POSITIONEN.MENGE) FROM POSITIONEN INNER JOIN BELEGE ON (POSITIONEN.BELEGTNRHASH=BELEGE.BELEGNRHASH and POSITIONEN.BELEGNR = BELEGE.BELEGNR) WHERE POSITIONEN.ARTIKELNR = '1090213000' AND BELEGE.BELEGART = 'A' ;

Mit dem Optimierer würde ich noch prüfen ob es Sinn macht, dass SQL auf SubQueries oder Left-Joins umzustellen.

MYSQL neigt gelegentlich dazu kartesische Produkte zu bilden.

Vielleicht so?

Code:

SELECT SUM(POSITIONEN.MENGE) FROM POSITIONEN 
JOIN
(select BELEGE.BELEGNRHASH, BELEGE.BELEGNR FROM BELEGE WHERE POSITIONEN.ARTIKELNR = '1090213000' AND BELEGE.BELEGART = 'A') abc on 
(POSITIONEN.BELEGTNRHASH=abc.BELEGNRHASH and POSITIONEN.BELEGNR = abc.BELEGNR)