nicht angemeldet
MySQL Performance-Problem bei Abfrage v. > 30 Feldern
Hallo,
ich muss eine Abfrage ausführen, die in allen 30 Feldern einer Tabelle Werte vergleicht.
Ich suche Datensätze deren Werte, ausser in 2 Feldern, NULL entsprechen. Die Tabelle enthält ca. 40.000 Einträge, das macht MySQL aber absolut nicht mit. Sobald ich mit LIMIT mehr als 100 Datensätze zurückgebe, dauert die Abfrage mitunter mehrere Minuten...
Gibt es eine Möglichkeit die Abfrage zu beschleunigen, bzw. wie sollte eine Abfrage für diesen Zweck idealerweise aussehen?
Steffen
-
Hallo!
ich muss eine Abfrage ausführen, die in allen 30 Feldern einer Tabelle Werte vergleicht.
Ich suche Datensätze deren Werte, ausser in 2 Feldern, NULL entsprechen. Die Tabelle enthält ca. 40.000 Einträge, das macht MySQL aber absolut nicht mit. Sobald ich mit LIMIT mehr als 100 Datensätze zurückgebe, dauert die Abfrage mitunter mehrere Minuten...Gibt es eine Möglichkeit die Abfrage zu beschleunigen, bzw. wie sollte eine Abfrage für diesen Zweck idealerweise aussehen?
Wie sieht den Deine jetziges SQL-Statemant aus? Wie ungefähr ist die Tabellenstruktur?
-
Wie sieht den Deine jetziges SQL-Statemant aus?
"SELECT id FROM session_data WHERE session_id != '' AND angebots_id > 0 AND g=0 AND kw=0 AND ... AND g_nachname = ''"
Wie ungefähr ist die Tabellenstruktur?
id int(11) - auto_increment
session_id varchar(40)
angebots_id int(11)
g int(1)
kw int(2)
jahr int(4)
personen int(2)
anreisetag varchar(10)
g_v int(1)
g_id_1 varchar(4)
g_id_2 varchar(4)
g_id_3 varchar(4)
rrv int(1)
anrede varchar(10)
vorname varchar(40)
nachname varchar(40)
strasse varchar(40)
nr varchar(6)
plz varchar(5)
ort varchar(40)
a_a int(1)
a_anrede varchar(10)
a_vorname varchar(40)
a_nachname varchar(40)
a_strasse varchar(40)
a_nr varchar(6)
a_plz varchar(5)
a_ort varchar(40)
tel varchar(20)
fax varchar(20)
email varchar(60)
pass varchar(12)
pass_w varchar(12)
bemerkung text
g_anrede varchar(10)
g_vorname varchar(40)
g_nachname varchar(40)-
Hallo!
"SELECT id FROM session_data WHERE session_id != '' AND angebots_id > 0 AND g=0 AND kw=0 AND ... AND g_nachname = ''"
Du hast sehr viele VARCHAR-Spalten, was natürlich nicht gerade performant ist. Du vergleichst nach Leerstrings. Wenn nichts eingetragen ist, dann soll die Spalte den Wert NULL annehemen.
vorname VARCHAR(20) NULL DEFAULT NULL;
Ersetze alle Datenwerte, die '' enthalten.
UPDATE session_data SET vorname=NULL WHERE vorname='';
Die Spalte mit den Postleitzahlen kann eine INT-Spalte werden, wenn es im deutsche PLZs geht.
Du suchst nach Datensätze, bei denen alle Datenwerte NULL oder bzw. '' sind, wegen "AND". Lege eine weitere Spalte an, die erst einen Wert, zm Beispiel 1 (TINYINT oder BOOL) annimmt, wenn etwas in den anderen Spalten eingetragen wird.
MfG, André Laugks
-
ReHallo!
Mache also alle Spalten, die ganze Zahlen enthalten, zu INT-Spalten. Daurch wird die Tabelle schneller. Spalten mit fester Länge sind immer schneller als Spalten mir variabler Länge.
MfG, André Laugks
-
Hallo...
Du hast sehr viele VARCHAR-Spalten, was natürlich nicht gerade performant ist.
Ja, leider - kann man halt nicht ändern...
Du vergleichst nach Leerstrings. Wenn nichts eingetragen ist, dann soll die
Spalte den Wert NULL annehemen.
vorname VARCHAR(20) NULL DEFAULT NULL;
Ersetze alle Datenwerte, die '' enthalten.
UPDATE session_data SET vorname=NULL WHERE vorname='';Ich dachte NULL wäre nur etwas Datenbank-Internes und MySQL gibt nichts, also '', zurück wenn NULL?
Die Spalte mit den Postleitzahlen kann eine INT-Spalte werden, wenn es im deutsche
PLZs geht.Leider gibt es auch Postleitzahlen die mit 0 beginnen, Dresden hat beispielsweise 01129, da wird bei INT als Feldtyp ja dann die 0 vorne abgeschnitten, oder gibt es da eine andere Lösung für?
Du suchst nach Datensätze, bei denen alle Datenwerte NULL oder bzw. '' sind, wegen
"AND". Lege eine weitere Spalte an, die erst einen Wert, zm Beispiel 1
(TINYINT oder BOOL) annimmt, wenn etwas in den anderen Spalten eingetragen wird.Ja, das ist natürlich eine Idee. Danke!
Ciao,
Steffen-
Hallo!
Du hast sehr viele VARCHAR-Spalten, was natürlich nicht gerade performant ist.
Ja, leider - kann man halt nicht ändern...
Versuche so viel INT-Spalten wie möglich zu verwenden. Aber wie ich sehe, ist das bei Dir nicht so möglich.
Ich dachte NULL wäre nur etwas Datenbank-Internes und MySQL gibt nichts, also '', zurück wenn NULL?
NULL ist nicht 0 oder '', sondern nichts... gar nichts. Du kannst ja nach NULL suchen: ... WHERE spalte IS NULL; ... WHERE spalte IS NOT NULL;
Leider gibt es auch Postleitzahlen die mit 0 beginnen, Dresden hat beispielsweise 01129, da wird bei INT als Feldtyp ja dann die 0 vorne abgeschnitten, oder gibt es da eine andere Lösung für?
Kannst Du kannst aber ZEROFILL verwenden.
plz INT(5) NULL ZEROFILE;
Nun wird von rechts nach links alles mit 0 aufgefüllt.
01129
00129
50666Du kannst das aber auch mit der Applikation lösen. Mit PHP könnte das so aussehen:
$plz = "1234";
$format_plz = sprintf("%05d", $plz);$format_plz --> 0124
MfG, André Laugks
-
Hallo zusammen,
Versuche so viel INT-Spalten wie möglich zu verwenden. Aber wie ich sehe, ist das bei Dir nicht so möglich.
"so viel wie möglich" bringt wenig, weil eine varchar-spalte ausreicht, um den Performance-Vorteil zunichte zu machen:
"You will get dynamic row length as soon as you are using a single VARCHAR or BLOB column." (MySQL-Manual)
Nur wenn alle Spalten feste Länge haben, gibt das einen Performancevorteil (weil MySQL dann sofort weiß, wo im isam-File Feld x von Datensatz y steht).
Normalerweise nehme ich persönlich immer char, wenn ich keine blobs/texts in der tabelle habe, weil mir dann das bißchen Speicherplatz, daß ich durch die varchars gewinne, den performancenachteil nicht wert ist (bei 40.000 Datensätzen mit einer
geschätzen Länge von 2k per Datensatz - ich habs' nicht zusammengerechnet - ergeben sich auch nur 80mb Datenbankgröße, wenn man stattdessen chars verwendet). Leider ist in der Tabellendefinition ein blob :-(, aber vielleicht reichen da ja auch 255 Zeichen?
Ansonsten - Steffen, hast Du Keys für die Spalten? Wenn Du wenig
Updates/Inserts machst, aber Probleme mit selects hast, könntest Du auch einfach für mehr Spalten einen Key definieren, dann geht es auch sehr viel schneller.Viele Grüße
Stephan-
"so viel wie möglich" bringt wenig, weil eine varchar-spalte ausreicht, um den
Performance-Vorteil zunichte zu machen:
"You will get dynamic row length as soon as you are using a single VARCHAR or
BLOB column." (MySQL-Manual)
Nur wenn alle Spalten feste Länge haben, gibt das einen Performancevorteil (weil MySQL
dann sofort weiß, wo im isam-File Feld x von Datensatz y steht).Ok, das ist interessant. Mir reichen 255 Zeichen für die Bemerkung, also mir sowieso, unseren Usern hoffentlich auch :-)
Dumm nur, dass MySQL mich nicht einfach in Char ändern lässt, bzw. automatisch wieder in Varchar ändert. Muss morgen mal die Tabelle neu anlegen und ausprobieren wieviel das bringt.
Danke für den Tip!
Ciao,
Steffen-
Hallo!
Dumm nur, dass MySQL mich nicht einfach in Char ändern lässt, bzw. automatisch wieder in Varchar ändert. Muss morgen mal die Tabelle neu anlegen und ausprobieren wieviel das bringt.
Sobald Du nur eine VARCHAR-Spalte anlegst, wandelt mySQL alle CHAR-Spalten größer 4 Zeichen in VARCHAR-Spalten um.
Nun habe ich mit wegen Stephan Huber seinem Beitrag noch mal das Buch zur Hand genommen.
-
Tabellen haben nur eine feste Länge, wenn alle spalten einen Spaltentpy emit fester Länge haben.
-
Weil es keine Performance mit Spalten variabler Länge verloren geht, werden die Spalten fester Länge in Spalten variabler Länge umgewandelt, weil das Speichervorteile bringt.
Das macht wohl nur mySQL so.
Du kannst nur alle Spalten in CHAR-Spalten umwandel, wenn Du es in einem Rutsch umwandelst.
ALTER TABLE tabelle MODIFY vorname CHAR(20), MODIFY nachname CHAR(20), ... usw.
Sobald Du es nur mit einer Spalte machst, wandelt mySQL sie wieder in VARCHAR um.
Aber Antje Hofmann hat auch noch was interessanten geschrieben. Vielleicht solltest Du über die Tabellenstruktur nachdenken...
Gar nicht so schlecht, daß ich mal wieder ins Buch geschauen habe ...
MfG, André Laugks
-
Dumm nur, dass MySQL mich nicht einfach in Char ändern lässt, bzw. automatisch wieder in
Varchar ändert. Muss morgen mal die Tabelle neu anlegen und ausprobieren wieviel das bringt.
Sobald Du nur eine VARCHAR-Spalte anlegst, wandelt mySQL alle CHAR-Spalten größer 4 Zeichen in VARCHAR-Spalten um.Ja, ich habe gestern einen Artikel zum Thema gefunden, da war das auch so erklärt.
- Weil es keine Performance mit Spalten variabler Länge verloren geht, werden die Spalten
fester Länge in Spalten variabler Länge umgewandelt, weil das Speichervorteile bringt.
Hmmm, also es bringt keine bessere Performance, aber Speichervorteile? In dem von mir angesprochenen Artikel stand genau das Gegenteil :-)
Du kannst nur alle Spalten in CHAR-Spalten umwandel, wenn Du es in einem Rutsch umwandelst.
ALTER TABLE tabelle MODIFY vorname CHAR(20), MODIFY nachname CHAR(20), ... usw.Danke, das wäre meine nächste Frage gewesen :-)
Aber Antje Hofmann hat auch noch was interessanten geschrieben. Vielleicht solltest Du
über die Tabellenstruktur nachdenken...Ja, prinzipiell hat sie nicht unrecht. Nur in diesem, meinem Fall nicht. Die Sessions werden sowieso in der Tabelle "sessions" verwaltet. Zur Session gehören noch einige weitere Felder, wie time, last_time, tracking_id, kunden_id, partner_id...
Und, nur für Besucher, die den Buchungsbereich betreten wird ein Eintrag in der Tabelle "session_data" gemacht - nicht für jeden Besucher. Ich muss diese Daten leider einige Wochen archivieren, aus Faulheit habe ich die entsprechenden Einträge einfach in der Tabelle belassen. Wir haben ne ganze Menge User, da kommt einiges zusammen, auch einfache Abfragen dauern einen Moment. Daher kam ich auf die Idee, "leere" Datensätze sofort zu löschen, da diese eben nicht archviert werden müssen....Gar nicht so schlecht, daß ich mal wieder ins Buch geschauen habe ...
Ja, ich sollte mir mal ein vernünftiges zum Thema MySQL kaufen, irgendeinen Vorschlag?
Danke an alle für die Hilfe!
Ciao,
Steffen
-
Hallo!
- Weil es keine Performance mit Spalten variabler Länge verloren geht, werden die Spalten
fester Länge in Spalten variabler Länge umgewandelt, weil das Speichervorteile bringt.
Hmmm, also es bringt keine bessere Performance, aber Speichervorteile? In dem von mir angesprochenen Artikel stand genau das Gegenteil :-)
Ohh Gott, was habe ich den da geschrieben. Der Satz ist auch etwas chaotisch. Das Bier was schuld ;-).
- Weil der Performancevorteil verloren geht, wenn auch nur eine Spalte variabler Länge verwendet wird, wandelt mySQL CHAR in VARCHAR um, weil dies Speichervorteil bring.
Ja, ich sollte mir mal ein vernünftiges zum Thema MySQL kaufen, irgendeinen Vorschlag?
Ich habe MySQL von Paul Dubois. Ich finde es besser als MySQL von Kofler. MySQL von Paul Dubois behandelt nicht die neueren Versionen von mySQL. INNER JOINs werden nicht besprochen. Auch von Transaktionen (Betazustand) ist nicht die Rede, die ja schon länger implementiert sind. Da geht zum Beispiel Kofler drauf ein. Auch einige neuere SQL Sachen kommen nicht im Dubois vor. Dubois hat bessere SQL-Beispiele als Kofler. Kofler gehr meiner Meinung nach, aber mehr und besser auf die Administration ein.
Dubois ist aber auch eine Übersetzung und schon was länger auf dem Markt als der Kofler.
Aber der Markt ist voll von MySQL-Büchern.MfG, André Laugks
-
Hi...
Ohh Gott, was habe ich den da geschrieben. Der Satz ist auch etwas chaotisch.
Das Bier was schuld ;-).Was, Du trinkst schon morgens?
Ja, ich sollte mir mal ein vernünftiges zum Thema MySQL kaufen, irgendeinen Vorschlag?
Ich habe MySQL von Paul Dubois. Ich finde es besser als MySQL von Kofler
...
Aber der Markt ist voll von MySQL-Büchern.Ja, das stimmt wohl. Ich habe mich für das Buch von Kofler entschieden. Mal sehen, vielleicht bin ich hier bald der MySQL-Guru :-)
Nochmals danke!
Steffen
-
Hallo!
Was, Du trinkst schon morgens?
Nein, Nein, ich bin morgens erst nach Hause gekommen und habe noch einmal ins Forum geschaut! ;-)
MfG, André Laugks
-
- Weil es keine Performance mit Spalten variabler Länge verloren geht, werden die Spalten
- Weil es keine Performance mit Spalten variabler Länge verloren geht, werden die Spalten
-
-
-
Hallo!
"so viel wie möglich" bringt wenig, weil eine varchar-spalte ausreicht, um den Performance-Vorteil zunichte zu machen:
"You will get dynamic row length as soon as you are using a single VARCHAR or BLOB column." (MySQL-Manual)OK! Daswar mir jetzt nicht bekannt. Mir war immer so, daß die Performance mit der Anzahl der Spalten variabler Länge in die Knie geht!
MfG, André Laugks
-
-
-
-
-
Hallo Steffen
"SELECT id FROM session_data WHERE session_id != '' AND angebots_id > 0 AND g=0 AND kw=0 AND ... AND g_nachname = ''"
Wie ungefähr ist die Tabellenstruktur?
id int(11) - auto_increment
session_id varchar(40)
angebots_id int(11)
g int(1)
kw int(2)
jahr int(4)
personen int(2)
anreisetag varchar(10)
g_v int(1)
g_id_1 varchar(4)
g_id_2 varchar(4)
g_id_3 varchar(4)
rrv int(1)
anrede varchar(10)
vorname varchar(40)
nachname varchar(40)
strasse varchar(40)
nr varchar(6)
plz varchar(5)
ort varchar(40)
a_a int(1)
a_anrede varchar(10)
a_vorname varchar(40)
a_nachname varchar(40)
a_strasse varchar(40)
a_nr varchar(6)
a_plz varchar(5)
a_ort varchar(40)
tel varchar(20)
fax varchar(20)
email varchar(60)
pass varchar(12)
pass_w varchar(12)
bemerkung text
g_anrede varchar(10)
g_vorname varchar(40)
g_nachname varchar(40)Wenn ich mir so die Struktur und deine Frage so ansehe, dann würde ich generell den Aufbau ändern.
Ich vermute, du legst für jeden Nutzer einen Eintrag in dieser Tabelle an. Ein Teil der Nutzer speichert Daten, der Rest nicht. Eventuell werden auch Nutzer mehrfach eingetragen.
So wie du vorgehst wird deine Nutzertabelle so gut wie gar nicht genutzt.
Ich würde aus dieser Tabelle mindestens 2 machen.
Tabelle 1: session
angebot_id session_id nutzer_id
Tabelle 2: nutzerdaten
der Rest
das Autoincrementfeld entspricht der nutzer_id in der session-Tabelle
speichert ein nutzer nun Nutzerdaten erfolgt zusätzlich zum Speichern der Daten ein update der sessiontabelle, und die nutzer_id wird eingetragen.
Auf die Art und Weise kostet dir dein Select so gut wie gar keine Zeit und du vermeidest überflüssige Datensätze in der Nutzertabelle.
Viele Grüße
Antje
-
-