Simple MySQL-Abfrage beschleunigen?

[Eddie]

Hallo allerseits,

ich habe ein Problem mit einer Datenbankabfrage, die einfach zu lange dauert - nämlich 6 Sekunden. Und leider habe ich keine wirkliche Idee, wie ich das beschleunigen kann :-(

Folgende Abfrage soll die Tabelle photos (mit 600.000 Einträgen) mit der Tabelle Artikel (mit 10.000 Einträgen) verknüpfen und einfach nur Photos zählen, die den beiden kommentierten Bedingungen genügen.

SELECT count(*) as numberOfPhotos  
FROM photo p, article a  
WHERE  
# Bedingung 1: deutsche Sprache  
a.language = 'de'  
# Bedingung 2: veröffentlichter Artikel  
AND a.status = 'published'  
AND p.fk_article = a.id;

Das Ergebnis dieser Abfrage ist 550.000 - und das braucht 6 Sekunden. Wenn ich die beiden Bedingungen testweise rausnehme, sind's nur noch 1,5 Sekunden.

Indizes sind meiner Meinung nach korrekt(?) gesetzt, haben jedoch für beide Bedingungspalten eine Kardinalität von 2 bzw. 3. Vielleicht liegt es ja daran?

Ansonsten wäre ich Euch sehr dankbar für jeden Hinweis, wie ich diese Abfrage ein wenig optimieren kann.

Eddie

[ChrisB]

Hi,

ich habe ein Problem mit einer Datenbankabfrage, die einfach zu lange dauert - nämlich 6 Sekunden. Und leider habe ich keine wirkliche Idee, wie ich das beschleunigen kann :-(

Der übliche erste Schritt: EXPLAIN vor das Statement setzen, damit die Datenbank dir erklärt, was genau sie bei der Ausführung macht.

MfG ChrisB

[Eddie]

Hi Chris,

Der übliche erste Schritt: EXPLAIN vor das Statement setzen, damit die Datenbank dir erklärt, was genau sie bei der Ausführung macht.

Ich verstehe das Ergebnis nur teilweise (recherchiere morgen, grad saumüde), vermute aber, dass die Analyse der verwendbaren Indizes das Wichtigste ist. Und die scheinen alle da zu sein. Oder fällt Dir was Kritisches auf?

id | select_type | table | type | possible_keys           | key        | key_len | ref          | rows | Extra  
1  | SIMPLE      | a     | ref  | PRIMARY,status,language | status     | 1       | const        | 3627 | Using where  
1  | SIMPLE      | p     | ref  | fk_article,status       | fk_article | 2       | mytable.a.id | 152  | Using where

Eddie

[T-Rex]

Probier doch mal einen Join aus.
So wie dein Explain aussieht, wird eine virtuelle Tabelle mit allen Kreuzungen gebildet. Aus der Tabelle werden dann erst in einem zweiten Schritt die Ergebnisse aussortiert.
Bei einem Join wird bereits beim erstellen der virtuellen Tabelle darauf geachtet, dass nicht alle Datensätze miteinander gekreuzt werden. Ergo müsste es schneller laufen.

Hoffe ich hab es richtig erklärt.

Gruß
JoinT-Rex

[hotti]

Hoffe ich hab es richtig erklärt.

Schreibs auf :)

  
SELECT count(*) as numberOfPhotos  
FROM photo  
JOIN article ON photo.fk_article = article.id  
WHERE article.language = 'de' AND article.status = 'published'  

ungetestet.

Schreibr, Horst

[dedlfix]

Tach!

Probier doch mal einen Join aus.

Da ist doch einer drin, ein impliziter.

FROM photo p, article a
WHERE [...] p.fk_article = a.id;

Es wird auch nicht besser, wenn man ihn zu einem expliziten umschreibt. FROM p JOIN a ON p.fk_article = a.id macht exakt daselbe.

So wie dein Explain aussieht, wird eine virtuelle Tabelle mit allen Kreuzungen gebildet. Aus der Tabelle werden dann erst in einem zweiten Schritt die Ergebnisse aussortiert.

Sieht aber nur so aus. Die Ausführungspläne beider Schreibweisen dürften sich nicht unterscheiden. (Ich bin mir da ziemlich sicher, habs aber grad nicht gegengeprüft, deswegen der Konjunktiv.)

dedlfix.

[SQL]

... einfach nur Photos zählen, die den beiden kommentierten Bedingungen genügen.

SELECT
  count(*) as numberOfPhotos
FROM
  photo p
WHERE
  EXISTS (SELECT 1 FROM article a WHERE a.id=pfk_article AND a.language = 'de' AND a.status = 'published')

[ralphi]

Hi,
Also wenn sich das Ergebnis nicht oft (schnell) ändert, würde ich an deiner stelle,

• entweder ein view
• oder eine eigene Tabelle
  mit den Daten wie count usw. erzeugen.
  Bei Änderung oder in einem Zeitintervall updaten.
  Dann hast du eine sehr einfache Abfrage in einer sehr kleinen Tabelle ;-)

Oder aber den Wert ‘count’ vorab in eine Sessionvar oder LocalStorage schreiben.

Viele Grüße aus LA

[Eddie]

Hi ralphi

ich nutze derzeit APC, das hat aber eine Menge "misses". Werde Deinen Ratschlag befolgen, und das Ergebnis irgendwo persistent zwischenspeichern. Muss mir noch überlegen wo :-)

Eddie

[Eddie]

Danke Euch allen! Sorry, ich war ein paar Tage nicht dran am Projekt.

Joins habe ich ausprobiert, es macht tatsächlich keinen Unterschied! Schade aber auch, das wär zu schön gewesen!

Eddie

[dedlfix]

Tach!

Joins habe ich ausprobiert, es macht tatsächlich keinen Unterschied!

Ja, weil sich dadurch nichts ändert außer der Schreibweise. Aber da gibts ja noch den Vorschlag mit dem EXISTS() vom Antworter namens SQL ...

dedlfix.