MYSQL langsamer als ACCESS was mache ich falsche

[TMaske77]

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

 

[Whiz-zarD]

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

Wo liegt denn da nun der Unterschied?
Wenn ich alle doppelten auflisten will, muss ich doch fragen, welche doppelt sind O_o

 

[J@ck]

Das Query ist viel zu umständlich formuliert.

Was auf die schnelle gut geht ist:

select für serienunmmer,

serienummer = variable_xY;

query: select serial from xy where serial != seriennummer;

geht auch locker mit einem select.
irgendwie so, rest findest sicher auch so herraus

 

[tm0975]

unterabfragen vermeiden.

select Serial
from scan_list
group by Serial
having count(*) > 1
order by serial

ich kann dir zwar für mysql keine empfehlung geben, aber grundsätzlich kannst du dir mal von der datenbank die abfragezerlegung und optimierung anzeigen lassen. dann siehst du, in welche schritte das dbms deine abfrage zerlegt und wie lange welcher schritt benötigt. beim sql-server geht das recht einfach.

 

[TMaske77]

Angenommen ich habe in der Datenbank folgende Zeilen

ID | Produkt | Serial | Timestamp
1 aaaaaa ab123 1.9.2010
2 bbbbbb cd456 1.9.2010
3 cccccccc ef789 1.9.2010
4 dddddd ab123 1.9.2010

dann möchte ich als Ergebnis folgende Ausgabe erhalten

1 aaaaaa ab123 1.9.2010
4 dddddd ab123 1.9.2010

Dies geht aber mit der Vorgeschlagenen Abfrage nicht - da ich dort nur
ab123 erhalten würde aber nicht die Produktbezeichnus dazu.

Bitte weitere sinnvolle Ideen.

 

[J@ck]

query: select serial, produkt from xy where serial != seriennummer;

 

[TMaske77]

Ein Explain zeigt mir folgende Info - vielleicht erkennt jemand hier einen Fehler

id 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

 

[J@ck]

ID (BIG INT 20)

würde ich auch ändern

 

[Holt]

Du hast keine Ahnung von Datenbanken, richtig? Also erstmal solltest Du angeben, was der Schlüssel der Tabelle sein soll. ID vermutlich? Oder mache Serial zum Schlüssel und füge ein Feld Count (Int) ein welches ebenfalls Schlüssel wird, dann prüfe Max(Count) where Serial= :SID vor dem Einfügen und füge mit MAX(Count) + 1 den neuen Datensatz ein. So kannst Du mit select * from scan_list where count > 1 alle Serials erfassen, die mehr als einmal erscheinen. Ggf. einen Index auf Count anlegen.
(Ob das mit MySQL geht, weiß ich nicht, da ich nur mit Oracle arbeite. Die XE Edition ist bis 3GB Daten frei.)

 

[tm0975]

dann erstellst du eine temp. zwischentabelle, so wie ich dir das weiter oben gezeigt habe. danach verknüpfst du mit der orignaltabelle für die noch benötigten spalten und schon bist du fertig. zwischentabellen erstellen ist auf jeden fall zu empfehlen. wie erstellen transaktionen, die teilweise über 20 a4-seiten lang sind. so kann man alles gut nachvollziehen.

 

[TMaske77]

@ Clusted

Deine Abfrage liefert folgendes Ergebnis
MySQL lieferte ein leeres Resultat zurück (d. h. null Zeilen). ( die Abfrage dauerte 0.0002 sek. )

Meine Abfrage liefert
Zeige Datensätze 0 - 1 (2 insgesamt, die Abfrage dauerte 0.0001 sek.)

@alle
Ich habe eine Lösung mit Zwischentabellen.
D.h. ich schreibe das Ergebnis der Select Abfrage aus der Where-Clausel in eine Temp-Tabelle und vergleiche dann dagegen - dann geht es in < 1 sek.
Aber das kann doch nicht die Lösung sein, den Access kann es ja auch unter 1sek direkt ohne die 2 Abfragen mit einem Insert in eine Temp-Tabelle dazwischen.

 

[karstent]

Hallo,

ich kenne mich mit MySQL leider nicht aus, aber vielleicht hilf folgendes weiter:

index auf serial

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)

Grüße
Karsten

 

[silvotin]

@TMaske77
Wieso vereinfachst du deine SQL Abfrage nicht auf den hinteren Teil?
"SELECT * FROM `table` WHERE serial <> 0 GROUP BY serial HAVING Count(*) > 1 ORDER BY serial, timestamp"

 

[no!dea]

kannst du mir davon nen dump zukommen lassen? dann spiel ich die db mal auf meinem webserver ein und mache nen vergleich

und erweitere das hier

select Serial
from scan_list
group by Serial
having count(*) > 1
order by serial

doch einfach auf

select Serial, spalte A, spalte B, spalte C
from scan_list
group by Serial
having count(*) > 1
order by serial

 

[TMaske77]

@foresnow
Weil dann das oben genannte Problem besteht --> Group --> d.h ich bekomme nicht die dazugehörigen Produkte

Ergänzung (4. September 2010)

@karstent

Dein Vorschlag ist die Lösung

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

mit dieser Abfrage geht es wesentlich schneller.

Ein explain zeigt das MYSQL dann die unter-Abfrage als ref und nicht als tmp macht

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

Gruß
Tobias

 

[no!dea]

danke für den dump!

meine ergebnisse:

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

= 3,85 sekunden


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

=0,02 sekunden




select Serial, id, orginal_scan, timestamp from scan_list group by Serial having count(*) > 1 order by serial;

= 0,01 sekunde

aber: ich bekomme jeweils unterschiedliche ergebnisse?!

 

[karstent]

@no!dea

Das kommt daher, dass die Gruppierung sich auf alle angezeigten Felder auswirkt.
Das bedeutet, dass Du nur komplette Dubletten (alle Felder stimmen überein) bekommst.

Das was TMaske77 haben wollte, sind die Datensätze, wo die serial Dubletten sind.

Du müsstest wahrscheinlich ein Ergebnis mit weniger Datensätzen haben.

Das hatte auch TMaske77 zu FireSnow Post kommentiert.

Durch einen exists erzeugt man kleine Zwischenergebnisse.
Durch die Verknüpfung über das indizierte serial Feld erreicht man einen schnellen Zugriff.
Durch 'x' erhält man eine Konstante zurück, und erspart sich das auslesen eines Feldes, welches ohnehin nicht interessant ist, da die Prüfung auf Existenz genügt.

So, genug geklugsch....

Grüße
Karsten

 

[TMaske77]

@karstent

Vielen Dank

Aber noch eine Frage.

Funktioniert das exists nur auf deine weise? Join auf 2 mal die gleiche Tabelle?

Oder kann ich das exists auch in meine Abfrage irgentwie einbauen?

Hab es wie folgt versucht erhalte aber einen Syntax fehler

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

 

[karstent]

@TMaske77

exists würde auch mit zwei verschiedenen Tabellen mit einem verknüpfbaren Feld funktionieren.

Ich kenne die MySQL Syntax nicht. Aber von SQL Sicht funktioniert Deine Abfrage nicht bzw. würde Dir nicht das richtige Ergebnis liefern, weil Du keine Referenz vom äusseren Select und dem subselect machst und am Ende sind zu viele Klammern.

Dadurch verringert sich Deine Menge nicht. Das äussere Select wird nicht von dem subselect beeinflußt.



Zu Deinem Beispiel:

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

Durch den Zusatz wird eine Referenz zwischen dem äusseren select und dem subselect hergestellt.

Dadurch wird die Ergebnismenge verringert.


Gib mir alle Datensätze wo die Bedingung zutrifft (es existiert ein Datensatz, wo die serials gleich sind).

Ich hoffe ich habe mich klar genug ausgedrückt.

Viele Grüße
Karsten

 

[TMaske77]

@ kartsent

Super Danke das passt und ist verständlich

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

Kannst du dir erklären, warum bei Access es auch anders geht?
Wenn man in Access eine Duplikatsabfrage über den Asistanten erstellt kommt genau das raus.