Datenbankoptimierung

[joah.]

Hallo,

hätte mal eine Frage an die Datenbankspezialisten an dieser Stelle. Ich arbeite gerade an einem neuen Kleinanzeigenmarkt. Wir rechnen, mit durchschnittlich 2 Millionen aktiven Anzeigen. Anfangs hatten wir auf die verschiedenen Rubriken runtergebrochen mehrere Tabellen, in denen die jeweiligen Anzeigen stehen, der Anzeigentext ist ausgelagert, ebenso div. Merkmale (z.B. Servolenkung, Klimaanlage bei Autos). Aus dem Anzeigentext werden Keywords (die wiederum in einer Keyword-KeywordID Tabelle ausgelagert sind) erzeugt, zusätzlich gibt es eine Features Tabelle (in der z.B. festgelegt wird, ob diverse kostenpflichtige Zusatzoptionen gebucht wurden), die wiederum alle zusammengejoint werden.

Somit ergeben sich relativ komplete SQL Statements, z.B.

SELECT a.anzeigenid, a.rubrikid, a.titel, a.sicht, a.preis, a.preisArt, a.erscheinungsdatum, a.plz AS anzeigePlz, a.status, features.fett, features.highlighted FROM anzeigen.sort_erscheinungsdatum_desc s, anzeigen.alle a LEFT JOIN anzeigen_features f ON a.anzeigenid=f.anzeigenid, mp_suchindex.keywords_10 AS k1,
mp_suchindex.keywords_10 AS k1,
merkmale_global.haarfarbe,
merkmale_global.augenfarbe,
merkmale_global.groesse
WHERE a.anzeigenid=s.anzeigenid AND a.rubrikid IN (16)
AND a.status='aktiv' AND a.erscheinungsdatum <= now()
AND a.anzeigenid=merkmale_global.haarfarbe.anzeigenid AND merkmale_global.haarfarbe.wert='rot'
AND a.anzeigenid=merkmale_global.augenfarbe.anzeigenid  AND merkmale_global.augenfarbe.wert='gruen'
AND a.anzeigenid=merkmale_global.groesse.anzeigenid AND merkmale_global.groesse.wert>'180'
AND a.anzeigenid=k1.anzeigenid AND k1.keyword='99217'
AND a.anzeigenid=k2.anzeigenid AND k2.keyword='99323'

ORDER BY erscheinungsdatum desc
LIMIT 0,50

Diese Suchanfrage findet in der Rubrik Kontakte Sie sucht Ihn alle rothaarigen, grünäugigen Damen bis 180 cm Größe, die das Keyword 99217 (finanziell) und das Keyword 99323 (unabhängig) besitzen.

Im Statement wird 2x mit verschiedenen Aliases auf die Keywordtabelle mit Keywords mit 10 Zeichen gejoint.

Momentan ist das ganze eigentlich relativ performant, die Frage, ist, was kann man tun, um es noch performanter zu bekommen?!

Ein Ansatz, den ich verfolgt habe, wäre das ORDER BY rauszunehmen, über einen CronJob eine weitere sortierte Tabelle mitaufzunehmen, so dass die Ergebnisse hier "vorsotiert" werden.

Was fallen euch hier für Möglichkeiten ein? Wie machen es die großen wie z.B. Ebay?

Danke für eure Hilfe.

Joah.

[Cheatah]

Hi,

Momentan ist das ganze eigentlich relativ performant, die Frage, ist, was kann man tun, um es noch performanter zu bekommen?!

welche Indexe hast Du eingesetzt?

Ein Ansatz, den ich verfolgt habe, wäre das ORDER BY rauszunehmen, über einen CronJob eine weitere sortierte Tabelle mitaufzunehmen, so dass die Ergebnisse hier "vorsotiert" werden.

Eine solche Tabelle nennt man "Index". Die Datenbank beherrscht das schon von sich aus.

Cheatah

[Joah.]

Hi,

Momentan ist das ganze eigentlich relativ performant, die Frage, ist, was kann man tun, um es noch performanter zu bekommen?!

welche Indexe hast Du eingesetzt?

Primärschlüssel liegt auf der AnzeigenID, Indexe liegen auf allen Feldern, die in der Projektion im SELECT ..... FROM liegen.

Ein Ansatz, den ich verfolgt habe, wäre das ORDER BY rauszunehmen, über einen CronJob eine weitere sortierte Tabelle mitaufzunehmen, so dass die Ergebnisse hier "vorsotiert" werden.

Eine solche Tabelle nennt man "Index". Die Datenbank beherrscht das schon von sich aus.

Ja kann sein, dass sie es beherrscht, hatte vergessen anzugeben, dass wir momentan MySQL in der Version 4 einsetzen. Habe gemerkt, dass wenn ich es ohne ORDER BY ausführe, es um ein Vielfaches schneller wird.

Cheatah

Gruß
Joah.

[Ilja]

yo,

Primärschlüssel liegt auf der AnzeigenID, Indexe liegen auf allen Feldern, die in der Projektion im SELECT ..... FROM liegen.

das macht wenig sinn, viel weichtiger sind solche spalten, über die gejoint wird oder aber im WHERE kriterium eine rolle spielen.

Ja kann sein, dass sie es beherrscht, hatte vergessen anzugeben, dass wir momentan MySQL in der Version 4 einsetzen. Habe gemerkt, dass wenn ich es ohne ORDER BY ausführe, es um ein Vielfaches schneller wird.

hast du dir schon mal einen ausführungsplan anzeigen lassen ?

Ilja

[Joah.]

Hi,

Primärschlüssel liegt auf der AnzeigenID, Indexe liegen auf allen Feldern, die in der Projektion im SELECT ..... FROM liegen.

das macht wenig sinn, viel weichtiger sind solche spalten, über die gejoint wird oder aber im WHERE kriterium eine rolle spielen.

ja eigentlich sind alle Spalten wichtig, Primärschlüssel und Fremdschlüssel sind alle indiziert. Wie siehts eigentlich aus, wenn ich anstatt einer MyISam mal eine InnoDB oder vergleichabres probiere, bringt das Performance? Wie wirkt sich das ganze mit dem Oracle DBMS aus?

Ja kann sein, dass sie es beherrscht, hatte vergessen anzugeben, dass wir momentan MySQL in der Version 4 einsetzen. Habe gemerkt, dass wenn ich es ohne ORDER BY ausführe, es um ein Vielfaches schneller wird.

hast du dir schon mal einen ausführungsplan anzeigen lassen ?

Ausführungsplan? ;) Wo gibts sowas? ;)

[Cheatah]

Hi,

ja eigentlich sind alle Spalten wichtig, Primärschlüssel und Fremdschlüssel sind alle indiziert.

die Datenbank verwendet pro Tabelle höchstens einen Index. Über welche Spalten und in welcher Reihenfolge sollte dieser gehen?

hast du dir schon mal einen ausführungsplan anzeigen lassen ?
Ausführungsplan? ;) Wo gibts sowas? ;)

Vorstellen von "EXPLAIN " vor das Statement.

Cheatah

[Joah.]

Hi,

ja eigentlich sind alle Spalten wichtig, Primärschlüssel und Fremdschlüssel sind alle indiziert.

die Datenbank verwendet pro Tabelle höchstens einen Index. Über welche Spalten und in welcher Reihenfolge sollte dieser gehen?

Über welche Spalten und in welcher reihenfolge sollte er denn gehen? ;)

hast du dir schon mal einen ausführungsplan anzeigen lassen ?
Ausführungsplan? ;) Wo gibts sowas? ;)

Vorstellen von "EXPLAIN " vor das Statement.

Achso, ja das habe ich auch schon gemacht. Ich habe halt in der anzeigen.alle Tabelle momentan knapp 2 Millionen Datensätze, wovon knapp 40% testweise in Rubrik 2 stehen. Hier dauert die Abfrage ca. 10 Sekunden, in den anderen Rubriken unter 1 Sekunde.

Joah.

[Cheatah]

Hi,

[Index]

Über welche Spalten und in welcher reihenfolge sollte er denn gehen? ;)

so, dass er möglichst schnell möglichst stark einschränkt. Es hängt also auch von den (zu erwartenden) Inhalten der Tabelle ab. Darüber hinaus sollte er effizient sein, was z.B. bedingt, dass Datumsspalten nur am Ende vorkommen sollten.

Cheatah

[Ilja]

yo,

die Datenbank verwendet pro Tabelle höchstens einen Index.

wäre mir neu.

Ilja

[Cheatah]

Hi,

die Datenbank verwendet pro Tabelle höchstens einen Index.
wäre mir neu.

wie soll sie denn in einem Statement eine Tabelle unter Verwendung mehrerer Indexe abarbeiten? Ich habe arge Schwierigkeiten, mir das organisatorisch vorzustellen.

Cheatah