MYQL langsamer als ACCESS was mache ich falsch

Ich habe nur eine Tabelle

Felder
1.) ID (BIG INT 20)
2.) Produkt (Char 255)
3.) Serial (Char 255)
4.) Timestamd (Timestamp)

Nun möchte ich alle doppelten Seriennummer aufliste - also genau und nicht nur welche ist doppelt

Ich habe hierfür folgende Abfrage
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

In der Datenbank sind 2500 Datensätze.
Führe ich die Abfrage auf meinem Quadcore Rechner aus dauert sie 5 Sek - führe ich Sie in der Datenbank meines Webspaces aus dauert sie ca 29 sek.

Ich habe es auf dem Webspace versucht um eine fehlerhafte Konfiguration meines XAMPP's auszuschliessen.

Da ich mir nicht vorstellen kann das eine Datenbank für eine solche Abfrage so lange benötigt habe ich das ganze in ACCESS 2003 gemacht und dort geht es in < 1 sek nicht messbar.

Ich habe schon viel mit Index..... im MYSQL versucht habe aber noch keine Lösung gefunden, wer kann mir einen Tip geben.

Was muss ich an der Abfrage ändern?
Was muss ich an der Tabelle Ändern?

Bin für jede Idee offen.
Danke

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Um herauszufinden, wie MySQL die Abfrage abarbeitet, gibt es den Befehl EXPLAIN. Als einfach nur EXPLAIN vor Deinen SQL schreiben und ausführen

Code:

mysql> EXPLAIN SELECT ...

Das Ergebnis dann hier gut formatiert mal zeigen. Ich gehe mal davon aus, das MySQL keine passenden Indices findet.

Alternativ kannst Du die Abfrage mal so umschreiben und nochmal testen (Ich hoffe, da ist jetzt kein Tippfehler drin):

Code:

SELECT sl.* 
  FROM scan_list sl
  JOIN (SELECT serial 
          FROM scan_list 
         WHERE serial <> "0" 
         GROUP BY serial 
         HAVING Count(*)>1 ) doppelt
    ON sl.serial = doppelt.serial
ORDER BY sl.serial;

Ausserdem fällt mir auf, dass Serial vom Typ CHAR(255) ist, muss das Feld wirklich so groß sein?

Grüße
Thomas

 

AW: MYQL langsamer als ACCESS was mache ich falsch

das Explain meiner Abfrage liefert folgendes Ergebnis

d select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY scan_list ALL NULL NULL NULL NULL 2575 Using where; Using filesort
2 DEPENDENT SUBQUERY Tmp range serial serial 17 NULL 2514 Using where; Using index

deine Abfrage geht viel schneller
und liefert folgendes Ergebnis

d select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1 PRIMARY sl ref serial serial 17 const 2
2 DERIVED scan_list index serial serial 17 NULL 2575 Using where; Using index


ich habe noch eine alternative gefunden diese liefert folgendes Ergebnis

select *
from scan_list s1
where exists (select 'x' from scan_list s2 where s2.serial=s1.serial and serial <>'0' group by serial having count(*) > 1)
order by serial, timestamp

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY s1 ALL NULL NULL NULL NULL 2575 Using where; Using filesort
2 DEPENDENT SUBQUERY s2 ref serial serial 17 serial_check.s1.serial 1 Using where; Using index

Ich frage mich immer noch wieso meine Abfrage in Access funktioniert und in MYSQL nicht --> wo ist der Unterschied zwischen deiner Abfrage und meiner

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Leider kann ich den EXPLAIN so nicht "lesen". Bitte exakt so wie die EXPLAIN Ausgabe ist (also mit dem Gitternetz, als Tabelle, als Textanhang an der Mail hier ) oder als Bild zeigen.

MySQL hält sich (teilweise) an den SQL-Standard. MS-Access siehe das manchmal anders. Welche MySQL Version ist das?

=> Bitte CODE-Tags benutzen.

Grüße
Thomas

 

AW: MYQL langsamer als ACCESS was mache ich falsch

1.)

Code:

 
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

2.)

Code:

 select * 
from scan_list s1
where exists (select 'x' from scan_list s2 where s2.serial=s1.serial and serial <>'0' group by serial having count(*) > 1)
order by serial, timestamp

Explain siehe Anhänge

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Okay, so läßt es sich gut betrachten.

Wie sieht der EXPLAIN von "meinem" SQL aus?

Könntest Du mal folgenden SQL ausführen und das Ergebnis zeigen?

Code:

SELECT COUNT(DISTINCT serial ), COUNT(*) FROM scan_list;

Grüße
Thomas

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Siehe Anhang

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Achso noch von deinem oben siehe Anhang

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Ich habe inzwischen in einem anderem Forum folgende Antwort erhalten.

Code:

 
SELECT * FROM scan_list s1
WHERE exists (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

Man muss das ganze über ein exists machen, dann geht es super schnell.
Bei Access funktioniert auch das where serial in --> bei MYSQL ist das super langsam

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Code:

SELECT COUNT(DISTINCT serial ), COUNT(*) FROM scan_list;

Hier meine ich das tatsächliche Ergebnis der Abfrage, da sich damit die Kardinalität der Spalte serial erkennen läßt.

Grüße
Thomas

 

AW: MYQL langsamer als ACCESS was mache ich falsch

COUNT(DISTINCT serial ) COUNT(*)
2481 2575

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Das solltest Du aber nicht zu "laut" sagen, denn bei dem Thema Crossposting werde einige sehr schnell sauer.

Laut EXPLAIN ist dieser SQL mit EXISTS identisch zu meinen mit dem JOIN.
Sind die Ausführzeiten identisch oder ähnlich?

Was ich gerade noch versuche ist herauszufinden, ob ein anderer Index etwas hilft.

Grüße
Thomas

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Eine Idee hätte ich noch..(Der Index auf serial existiert ja schon)

Code:

SELECT sl.* 
  FROM scan_list sl
  JOIN (SELECT serial 
          FROM scan_list 
         GROUP BY serial 
         HAVING Count(*)>1 ) AS doppelt
    ON sl.serial = doppelt.serial
 WHERE sl.serial <> "0" 
ORDER BY sl.serial;

Ansonsten ist morgen auch noch ein Tag und wenn nicht, dann ist es auch egal.

Grüße
Thomas

 

AW: MYQL langsamer als ACCESS was mache ich falsch

Ok lass gut sein -> Danke für den Hinweis.

Die beste und schnellste Lösung ist folgende.

Code:

SELECT * FROM scan_list s1
WHERE exists (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

Es sieht so aus, als wäre der Access Optimizer besser - habe folgende Aussage erhalten --> vielleicht müsste dies auch bei MYSQL umgesetzt werden

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.

 

AW: MYQL langsamer als ACCESS was mache ich falsch

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