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

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

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)

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

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

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.

Mein MS-SqlServer sagt:

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.