MYSQL langsamer als ACCESS was mache ich falsche

[karstent]

@TMaske77

Die Abfrage aus Access ist auch ok. Aber da MySQL es anscheinen nicht so gut verarbeitet bzw. dessen Optimizer nicht damit klar kommt, kann man es anders formulieren.

Manche DB Engines / Optimizer kommen mit einer anderen Formulierung besser klar.

Zu Deinem Beispiel:

index auf serial

select *
from scan_list
where serial in (select serial from scanlist where serial <>'0' group by serial having count >1)
order by serial, timestamp

Sollte auch funktionieren und bei der geringen Menge an Daten sollte es nicht lange dauern. (kein Unterschied bemerkbar)
Es kann aber gut sein, dass der Alias TMP den Optimizer verwirrt hat und die DB den Index nicht verwendet. Das von Access generierte Statement ist evtl. auf JET SQL optimiert.

Generell (ausser irgendwelche Spezialfälle) ist es besser eine exists Abfrage zu machen statt ein IN.

Bei Oracle vor 10g R2 gab es auch einen Bug. Bei neueren Versionen schreibt der Optimizer die Abfrage von IN in eine EXISTS Abfrage um.

Vielleicht kannst Du mal es ohne Alias TMP (so wie oben geschrieben) ausprobieren.
Bei der geringen Datenmenge sollte es eigentlich schnell gehen.

Grüße
Karsten

 

[TMaske77]

An dem Alias liegt es nicht --> auch nach dem entfernen dauert es noch genauso lange wie vorher.

Zeige Datensätze 0 - 5 (6 insgesamt, die Abfrage dauerte 4.8664 sek.) --> IN
Zeige Datensätze 0 - 5 (6 insgesamt, die Abfrage dauerte 0.0326 sek.) --> Exists

der Unterschied ist schon sehr groß

 

[karstent]

Vielen Dank für das Testen. Ist interessant. MySQL scheint mit dem IN Operator nicht so gut klar zu kommen.

Kannst Du bitte, wenn Du Zeit hast mal ANY oder SOME ausprobieren, ob es schneller als IN ist?

Es sollte in etwa so aussehen:

select *
from scan_list
where serial = ANY (select serial from scan_list where serial <>'0' group by serial having count >1)
order by serial, timestamp


Evtl. kannst Du auch folgendes Statement ausprobieren:
Das Statement als normales JOIN umformuliert.

select s1.id, s1.produkt, s1.serial, s1.timestamp
from scan_list s1, (select serial from scan_list where serial <>'0' group by serial having count >1) s2
where s1.serial=s2.serial
order by s1.serial, s1.timestamp

Vielen Dank im Voraus.

Grüße
Karsten

 

[TMaske77]

Zeige Datensätze 0 - 5 (6 insgesamt, die Abfrage dauerte 4.8584 sek.) --> ANY
Zeige Datensätze 0 - 5 (6 insgesamt, die Abfrage dauerte 0.0024 sek.) --> Join

Ich frage mich die ganze Zeit ob der Optimzier bei MYSQL umkonfiguriert werden kann,
da es aber selbst bei meinem großen Webhoster so ist - denke ich das es nicht geht.

Ich habe mir den Unterschied nochmal angeschaut und herrausgefunden, das es auch mit dem exists funktioniert jedoch muss die Abfrage im Vergleich zu Access etwas angepasst werden.

Query Access

SELECT * FROM scan_list
WHERE (((serial) In (SELECT serial FROM scan_list As Tmp
where serial <> "0" GROUP BY serial HAVING Count(*)>1 )))
ORDER BY serial, timestamp


Query Mysql

SELECT * FROM scan_list s1
WHERE (((serial) IN (SELECT serial FROM scan_list AS Tmp
where s1.serial = Tmp.serial AND serial <> "0" GROUP BY serial HAVING Count( * ) >1)))
ORDER BY serial, timestamp

Hier noch ein Vergleich der Zeiten bei 2500 Datensätzen

Zeige Datensätze 0 - 5 (6 insgesamt, die Abfrage dauerte 4.9410 sek.) = orginal Querry = Access
Zeige Datensätze 0 - 5 (6 insgesamt, die Abfrage dauerte 0.0327 sek.) = geänderte Querry = MySQL

 

[AlbertLast]

ich hab hier in ein paar vm einige rdbms bin gerade
am testen von dein query 178k datensätze sind es hier ...

mysql mit exists und in war es quasie nicht zulösen (über 10min) aber mit join
3,5 sek myisam 12,5 innodb
mit index auf pmfrom

SELECT s1.* FROM pn s1
join (SELECT pmfrom 
	FROM pn
	where pmfrom <> '0'
	GROUP BY pmfrom
	HAVING Count(pmfrom)>1 ) as s2 
   on s1.pmfrom = s2.pmfrom 
ORDER BY s1.pmfrom , s1.dateline
limit 1;

in postgres ging es in 3 sek, 1,5 sek mit index
postgres hat noch die ähnliche syntax zu mysql

SELECT * FROM pn s1
WHERE pmfrom in (SELECT pmfrom 
	FROM pn As tmp
	where pmfrom <> '0'
	GROUP BY pmfrom
	HAVING Count(pmfrom)>1 )
ORDER BY pmfrom , dateline
limit 1;

oracle xe 10g
0,5 sek exists und in sind gleich schnell
join ist vieleicht ein bissin langsamer aber auch im 0,5 sek bereich

SELECT * FROM pn s1
WHERE s1.pmfrom in (SELECT pmfrom 
	FROM pn
	where pmfrom <> '0'
	GROUP BY pmfrom
	HAVING Count(pmfrom)>1 ) and rownum = 1
ORDER BY s1.pmfrom , s1.dateline;

db sql 2k8 sp2
in,exists und join gleich schnell
6 sek

SELECT top 1 * FROM pn s1
WHERE exists (SELECT b.pmfrom 
	FROM pn b
	where b.pmfrom <> '0' and b.pmfrom = s1.pmfrom
	GROUP BY b.pmfrom
	HAVING Count(b.pmfrom)>1 )
ORDER BY s1.pmfrom , s1.dateline;

done

 

[TMaske77]

Versuch mal in MYSQL mein Ansatz von oben.
So habe ich es umgeschrieben und bei 2500 Datensätzen geht es bei mir super schnell

SELECT * FROM scan_list s1
WHERE (((serial) IN (SELECT serial FROM scan_list AS Tmp
where s1.serial = Tmp.serial AND serial <> "0" GROUP BY serial HAVING Count( * ) >1)))
ORDER BY serial, timestamp

 

[AlbertLast]

pmfrom ist ein varchar sollte ich vieleicht erwähnen

ausser das du viel unnötige klammern benutzt sehe ich keine unterschied zu dem
in und exists welches ich benutzt habe ... aber ich versuch mal durch laufen zu lassen

anscheinend bist du auch mit exists und in durch einander gekommen
weil es macht kein sin bei ein in ein where bedinung wie s1.serial = Tmp.serial diese zu machen

30sek

SELECT * FROM pn s1
WHERE pmfrom IN (SELECT pmfrom
	FROM pn AS tmp
	where s1.pmfrom = tmp.pmfrom AND tmp.pmfrom <> "0"
	GROUP BY pmfrom
	HAVING Count( pmfrom ) >1)
ORDER BY pmfrom, dateline
limit 1;

vieleicht sollte ich noch mal ein durchlauf ohne limit zu machen ...

Ergänzung (5. September 2010)

so schnell noch mal ohne limit gemacht ..

innodb:
in 44 sek
join 17 sek

myisam:
in 36 sek
join 7 sek

oracle:
in 15 sek
join 14 sek

sql 2k8:
join / in 18 sek
exists 16

pg:
in 15 sek
join 12 sek

 

[IceMatrix]

Wenn du mit Abfragen experimentierst, dann nimm dir IMMER den EXPLAIN vom MySQL-Server zur Grundlage. Lies dir dazu bitte die Dokumentation von MySQL durch. Dort wird beschrieben, was die einzelnen Werte dort bedeuten. Ziel ist es v.a. temporäre Tabellen und Filesorts zu beseitigen! Außerdem sollte möglichst jede Teilabfrage indexiert sein sofern wie ne bestimmte Zahl von Zeilen durchsucht.
Es kommt gelegentlich auch darauf an in welcher Folge die Teilabfragen ausgeführt werden, da es bei JOINs z.B. relevant ist die Anzahl der zu joinenden Zeilen gering zu halten.

 

[ice-breaker]

dein Query ist hochgradigst ineffizient, was erwartest du denn?

Du hast erstmal einen dependend Subquery, das ist schon der erste Fehler, und dann muss dieser depenendend Subquery auch noch Daten groupen, das kann niemals nie in einer relationalen Datenbank effizient sein.

Du wirst dein Datenbank-Schema umdenken müssen. (Folgendes bezieht sich auf AlbertLasts Query, die sind besser formatiert und somit leichter lesbar Speicher den COUNT(pmfrom) Wert in irgendeiner Tabelle ab, und aktualisiere ihn immerwieder, wenn sich die Daten ändern. Dann kannst du deinen Subquery zu einem Join umformulieren und der Query wird in weit unter einer Sekunde fertig sein.


Dieses Fehldesign findet man immerwieder, wenn langsame Datenbanken untersucht und verbessert werden sollen.

 

[AlbertLast]

das man count in eine tabelle speichert ist
gegen die normalisierung daher wenn man
sowas macht sollte man sich das bewusst sein und
sicherheitsmass nahmen einrichten.

und ja normalisierung macht eine db langsamer aber
sollte erstmal verstehen bevor man sich darüber weg setzt.

 

[Holt]

Hatte ich doch schon geschrieben, schnell wird es nur, wenn Du die Information nach der Du fragst auch speicherst. Ist nicht Normalform, aber was willst letztlich?
Statt Count bei jeder Abfrage neu zu berechnen, solltest Du es in der DB speichern und beim Einfügen und Löschen der Datensätze akutalisieren. Sofern Abfragen deutlich häufiger als Einfügen und Löschen ist, sollte die Gesamtperformance besser sein.