MySQL-Abfrage optimieren

L

Logigoo

Guest
Hallo,

mein problem ist das die SQL-Abfrage doch schon sehr langsam ist. Vielleicht könntet ihr mir ja Tipps geben wie ich die SQL-Abfrage bzw. den Index weiter optimieren könnte.
Ich kann mir nicht vorstellen das mit dem Datenbestand bereits die Grenze von MySQL erreicht ist
biggrin.gif


In der Tabelle "tbl_suchindex" wird jedes einzelen Wort aus dem Titel in einer eigene Zeile gespeichert und in die Spalte "relevanz" wird dann z.b. 100 eingetragen um jeden einzelnen "tag" eine ensprechende Gewichtung zu geben. Die Resultaten ensprechen auch genau meine Vorstellungen nur leider macht mir die Geschwindigkeit probleme. Und geplant sind ca. 600.000 Artikel. Demnach würder die Tabelle "tbl_suchindex" noch größer werden und die Abfrage noch länger dauern
sad.gif


Hier mal die Tabellen:

"tbl_artikel": ca. 280.000 Datensätze
id_artikel int(10) NOT NULL auto_increment (PRIMARY)
titel varchar(255) NOT NULL
aktiv tinyint(4) NOT NULL (INDEX)

"tbl_suchindex": ca. 3.800.000 Datensätze
id_artikel int(10) unsigned NOT NULL (INDEX)
tag varchar(150) default NULL (INDEX)
relevanz tinyint(1) unsigned NOT NULL
[/B]

Hier die eigentliche SQL-Abfrage (Dahinter steht eine Suchfunktion die nach Relevanz/Score sortiert)

CODE
SELECT
sum(a.relevanz) as score,
b.id_artikel,
b.titel
FROM
tbl_suchindex a
INNER JOIN
tbl_artikel b ON (b.id_artikel=a.id_artikel)
WHERE
a.tag IN ('suchwort1','suchwort2') AND
b.aktiv = 1
GROUP BY
a.id_artikel
ORDER BY
score DESC,
b.titel ASC
LIMIT 1000




Hier auch mal die Werte wenn ich es mit EXPLAIN ausführe.
Um es besser lesen zu können hab ich mal die einzelnen Spalten untereinander geschrieben.

id
1

1

select_type
SIMPLE
SIMPLE

table
a
b

type
ref
eq_ref

possible_keys
tag
PRIMARY

key
tag
PRIMARY

key_len
48
4

ref
const
dbxxx.a.id_artikel

rows
11172
1

Extra
Using where; Using temporary; Using filesort
Using where

Vielen Dank für eure Tipps
 
Ein paar Anmerkungen:

1. Die Tabellen sind nicht sauber normalisiert, Tags sind hochgradig redundant. Rausziehen in eine Randtabelle, so daß die Verknüpfungstabelle nur noch Verweise und einen Primärschlüssel hat. Letzterer fehlt, das ist immer schlecht.

2. Die Sql-Abfrage würde auf dem MS-SqlServer und auf Access wegen Syntaxfehlern abgelehnt werden. Was mySql daraus macht, weiß ich nicht so genau. Jedenfalls wird effektiv nach b.id_artikel, b.titel gruppiert, das ist extrem teuer, weil es genügen würde, nach a.id_artikel zu gruppieren.

3. Die Gruppierung sollte nicht mit einer Tabellenverknüpfung kombiniert werden, das bläht die Zahl der Datensätze nur sinnfrei auf. Also die Gruppierung über eine sortierte Unterabfrage erledigen, die nur die benötigte Zahl von Zeilen zurückgibt, erst dann die weiteren Daten (Tag) zusammensammeln.

4. 1000 Datensätze sind unnötig viel. Wahrscheinlich werden ohnehin nur sehr viel weniger angezeigt, also kann man das auch entsprechend einschränken.

Wahrscheinlich hilft das bereits dramatisch. Sollte das nichts helfen, könnte man sich auch Gedanken darüber machen, die Spitzenwerte (die sich ja nicht allzu häufig ändern) nächtlich zu ermitteln und diese in einer Tabelle (Detailtabelle, die Tags mit sich selbst verknüpft) zu speichern.
 
Vielen Dank für die schnelle Antwort.

Zu 1:
Meinen Sie einen Tabellenaufbau in folgender Form?

"tbl_artikel_suche_tag"
id_tag (PRIMARY)
tag

"tbl_artikel_suche_index"
id_index (PRIMARY)
id_artikel (INDEX)
id_tag (INDEX)
relevanz


Zu 2:
Könnten Sie mir bitte sagen wo bei der SQL-Abfrage der Syntaxfehlern auftreten würde?

Zu Punkt 3 und 4 komme ich später nochmal drauf zurück
wink.gif
 
Mit fällt da gerade noch ein das man auch bei Abfragen die oft auftreten Prozeduren schreiben kann, macht die Sache bedeutent schneller!

Grüße
 
QUOTE (Logigoo @ Mi 25.06.2008, 15:41)Zu 1:
Meinen Sie einen Tabellenaufbau in folgender Form?

"tbl_artikel_suche_tag"
id_tag (PRIMARY)
tag

"tbl_artikel_suche_index"
id_index (PRIMARY)
id_artikel (INDEX)
id_tag (INDEX)
relevanz


Im Prinzip ja, allerdings würde ich die tbl_artikel_suche_tag direkt als tbl_tag benennen, die zweite Grundtabelle heißt ja schon tbl_artikel, die dritte tbl_suchindex = tbl_artikel_suche_index.


QUOTE (Logigoo @ Mi 25.06.2008, 15:41)Könnten Sie mir bitte sagen wo bei der SQL-Abfrage der Syntaxfehlern auftreten würde?


Mein MS-SqlServer sagt:


QUOTE Meldung 8120, Ebene 16, Status 1, Zeile 2
Column 'tbl_artikel.id_artikel' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Das gehört zum Thema Group By.

mySql fördert da leider eine extrem schlampige Syntax, die im Standard-Sql gar nicht akzeptiert wird.
 
Ich würde die Tabellen ändern wie es @jAuer beschrieben hat und zusätzlich eine View anlegen die du gleichzeitig Sortierst mit deinen Order By und auf dieser View mit den Limit gehst, da ansonsten in deiner Abfrage dein Limit keine Performance bring da du Sortierst und Mysql erst das Gesamtergebnis sortieren muss und dann mit Limit die ersten 1000 Sätze ausgibt.

Erfolgt die Sortierung mithilfe eines Indexes (in deinen Fall nicht) ist der Limit befehl sehr schnell.

Gruß
Frank
 
Vielen Dank für die Hilfreichen Antworten.

Ich werde mich jetzt mal mit den Unterabfragen von MySQL beschäftigen um die Tabellenstruktur wie von @jAuer beschrieben zu realisieren.
Bisher hatte ich mit Unterabfragen noch nie was gemacht.
 
Sie müssen sich einloggen oder registrieren, um hier antworten zu können.
Zurück
Oben