SQL-Abfrage zwingt Server in die Knie

[mo]

Hallo

Ich muss folgende SQL-Query an eine mySQL-Datenbank absetzen, welche ca 40MB Daten enthält.Die Abfrage geht über zwei Tabellen, welche aber blöderweise identische Spaltennamen mit jedoch unterschiedlichen Inhalten haben.
Führe ich die Abfrage aus, geht der Server in die Knie.
Ich kann jedoch auch die Struktur ud die Spaltennamen nicht ändern, da diese jede Nacht aus einer Informix Datenbank neu erzeugt wird.
Und diese zu ändern, liegt leider nicht in meinem Einflussbereich.

Wie könnte ich die Abfrage optimieren?
Bin für jeden Tipp dankbar

Query:
SELECT a.name_1, b.name_1, b.name_2, b.telefon, b.email, b.konto FROM g600 AS a, g620 AS b WHERE a.konto=b.konto AND a.name_1 LIKE '%meier%' OR b.name_1 LIKE '%meier%' OR b.name_2 LIKE '%meier%' OR b.telefon LIKE '%meier%' OR b.email LIKE '%meier%' OR b.konto LIKE '%meier%' ORDER BY a.name_1 ASC, b.name_1 ASC;

Gruß

MO

[Philipp Hasenfratz]

Halihallo mo

Führe ich die Abfrage aus, geht der Server in die Knie.

Das liegt bei der kleinen Datenmenge sicher nicht an MySQL, sondern am Server.

Wie könnte ich die Abfrage optimieren?
Query:
SELECT a.name_1, b.name_1, b.name_2, b.telefon, b.email, b.konto FROM g600 AS a, g620 AS b WHERE a.konto=b.konto AND a.name_1 LIKE '%meier%' OR b.name_1 LIKE '%meier%' OR b.name_2 LIKE '%meier%' OR b.telefon LIKE '%meier%' OR b.email LIKE '%meier%' OR b.konto LIKE '%meier%' ORDER BY a.name_1 ASC, b.name_1 ASC;

LIKE '%...%' sind absolute Performancefresser, derartige Konstruktur erfodern einen
Full Table Scan => jeden einzelnen Record durchforsten. Wenn es die Aufgabestellung
ermöglicht, ist LIKE '...%' (ohne erstes %) mit Index tausendmal effizienter.

Keine Ahnung, warum der MySQL-Server aussteigt, ich würde mal versuchen neu zu
installieren, da dieses Verhalten bei 40MB nicht wirklich glaubhaft ist.

Kannst auch versuchen mit temporären Tabellen zu arbeiten (Speicherung aber dennoch
auf Platte). Beide Tabellen in eine schreiben, sodass die Datenbank keinen JOIN berechnen
muss (MySQL versucht die Daten erst im RAM-Speicher zu halten), damit verbrauchst du
weniger RAM-Speicher, aber mehr Performance (kommt eben darauf an, _was_ die DB in die
Knie zwingt).

Viele Grüsse

Philipp

[mo]

Das liegt bei der kleinen Datenmenge sicher nicht an MySQL, sondern am Server....

Jo....eher an der Abrfrage, schon klar.

LIKE '%...%' sind absolute Performancefresser, derartige Konstruktur erfodern einen
Full Table Scan => jeden einzelnen Record durchforsten. Wenn es die Aufgabestellung
ermöglicht, ist LIKE '...%' (ohne erstes %) mit Index tausendmal effizienter....

Hab schon Indexe über die zu durchsuchenden Spalten gelgt, hat aber nix gebracht. Das '%Suchbegriff%' ist notwendig, da bei der eingabe von 'meier' auch Spalten mit 'Obermeier' oder 'Meierhofer' ausgegeben werden sollen.

Keine Ahnung, warum der MySQL-Server aussteigt, ich würde mal versuchen neu zu
installieren, da dieses Verhalten bei 40MB nicht wirklich glaubhaft ist.....

Liegt nicht an der Installation. Denn sonst ist alles einwandfrei.
Nur bei dieser einen Abfrage.....

Kannst auch versuchen mit temporären Tabellen zu arbeiten (Speicherung aber dennoch
auf Platte). Beide Tabellen in eine schreiben, sodass die Datenbank keinen JOIN berechnen
muss (MySQL versucht die Daten erst im RAM-Speicher zu halten), damit verbrauchst du
weniger RAM-Speicher, aber mehr Performance (kommt eben darauf an, _was_ die DB in die
Knie zwingt)......

Wie arbeitet man mit temporären Tabellen?
Das habe ich nun noch nie gemacht

Trotzdem vielen Dank

Gruß

MO

[Philipp Hasenfratz]

Halihallo mo

Das liegt bei der kleinen Datenmenge sicher nicht an MySQL, sondern am Server....
Jo....eher an der Abrfrage, schon klar.

Entweder die, oder der Rechner, der etwas spinnt ;)

LIKE '%...%' sind absolute Performancefresser, derartige Konstruktur erfodern einen
Full Table Scan => jeden einzelnen Record durchforsten. Wenn es die Aufgabestellung
ermöglicht, ist LIKE '...%' (ohne erstes %) mit Index tausendmal effizienter....
Hab schon Indexe über die zu durchsuchenden Spalten gelgt, hat aber nix gebracht. Das '%Suchbegriff%' ist notwendig, da bei der eingabe von 'meier' auch Spalten mit 'Obermeier' oder 'Meierhofer' ausgegeben werden sollen.

Die Indizies brauchen in diesem Fall nur Speicher, wenn du die gegebenen LIKE Konstrukte
weiterverwendest. Der Index kann nicht verwendet werden.

Kannst auch versuchen mit temporären Tabellen zu arbeiten (Speicherung aber dennoch
auf Platte). Beide Tabellen in eine schreiben, sodass die Datenbank keinen JOIN berechnen
muss (MySQL versucht die Daten erst im RAM-Speicher zu halten), damit verbrauchst du
weniger RAM-Speicher, aber mehr Performance (kommt eben darauf an, _was_ die DB in die
Knie zwingt)......
Wie arbeitet man mit temporären Tabellen?
Das habe ich nun noch nie gemacht

Temporär war vielleicht missverständlich. Kannst dich doch mal über HEAP-Tables
unter www.MySQL.com informieren. Ich dachte aber daran, dass du eine ganz "normale"
Tabelle erstellst, und dort alle Datensätze speicherst; somit muss MySQL keinen
JOIN bilden. Ich bin mir jedoch nicht sicher, inwieweit MySQL bereits optimiert.

EXPLAIN SELECT <dein-query>

wenn er hier nicht abstürtzt, kann dir der Befehl nützliche Informationen bringen.

---

Arbeitest du mit einer Programmiersprache zusammen? - Dann könntest du mehrere
Unterabfragen (was sonst über Subselects ginge, jedoch nicht in MySQL) getrennt ausführen
und so die Last verteilen.

---

Grundsätzlich: Du musst ein Ziel verfolgen: Die Datenmenge möglichst schnell, möglichst
klein werden zu lassen (durch geeignete Kriterien); normalerweise versucht dies die
Datenbank automatisch, aber du scheinst hier etwas nachhelfen zu müssen. Deswegen mein
obiger Vorschlag auf eine Programmiersprache auszuweichen und mehrere Queries
auszuführen. Somit kannst du die Menge passender Datensätze schrittweise verkleinern
und die Datenbank muss nicht alles auf einmal durchsuchen. Geht zwar auf Kosten der
Performance, dafür funktioniert es vielleicht.

Viele Grüsse

Philipp

[Michael Schröpl]

Hi mo,

Ich kann jedoch auch die Struktur ud die Spaltennamen nicht ändern, da diese jede Nacht aus einer Informix Datenbank neu erzeugt wird.
Und diese zu ändern, liegt leider nicht in meinem Einflussbereich.

dann ist Deine Aufgabe wahrscheinlich nicht lösbar.

Eine hochperformante Substring-Suche würdest Du statt mit Deiner LIKE-Konstruktion mit Präfix-Wildcard nur dadurch hin bekommen, daß Du Deine Architektur völlig über den Haufen wirfst (_zusätzliche_ Tabellen, völlig andere Queries etc.).
Die Original-Tabellen dürften erhalten bleiben - nur würdest Du nicht in ihnen suchen, sondern sie nur zur "Übersetzung" der Treffer verwenden.

Wir haben ausführliche Diskussionen über Dein Problem im Archiv gespeichert ...

Viele Grüße
      Michael