1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen
  2. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

MYQL langsamer als ACCESS was mache ich falsch

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von TMaske77, 4 September 2010.

  1. TMaske77

    TMaske77 Benutzer

    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
     
  2. thomas_w

    thomas_w Datenbank-Guru

    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
     
  3. TMaske77

    TMaske77 Benutzer

    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
     
  4. thomas_w

    thomas_w Datenbank-Guru

    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
     
  5. TMaske77

    TMaske77 Benutzer

    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
     

    Anhänge:

    • 1.jpg
      1.jpg
      Dateigröße:
      9,6 KB
      Aufrufe:
      3
    • 2.jpg
      2.jpg
      Dateigröße:
      8,7 KB
      Aufrufe:
      3
  6. thomas_w

    thomas_w Datenbank-Guru

    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
     
  7. TMaske77

    TMaske77 Benutzer

    AW: MYQL langsamer als ACCESS was mache ich falsch

    Siehe Anhang
     

    Anhänge:

    • 3.png
      3.png
      Dateigröße:
      3,7 KB
      Aufrufe:
      3
  8. TMaske77

    TMaske77 Benutzer

    AW: MYQL langsamer als ACCESS was mache ich falsch

    Achso noch von deinem oben siehe Anhang
     

    Anhänge:

    • 4.jpg
      4.jpg
      Dateigröße:
      12 KB
      Aufrufe:
      3
  9. TMaske77

    TMaske77 Benutzer

    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
     
  10. thomas_w

    thomas_w Datenbank-Guru

    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
     
  11. TMaske77

    TMaske77 Benutzer

    AW: MYQL langsamer als ACCESS was mache ich falsch

    COUNT(DISTINCT serial ) COUNT(*)
    2481 2575
     
  12. thomas_w

    thomas_w Datenbank-Guru

    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
     
  13. thomas_w

    thomas_w Datenbank-Guru

    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
     
  14. TMaske77

    TMaske77 Benutzer

    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.
     
  15. TMaske77

    TMaske77 Benutzer

    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
     

Diese Seite empfehlen