Index aber richtig gesetzt

**TheFish511** · 30.05.2008, 21:33

Hmm, welche Datenbank verwendest du eigentlich?
Ich geh einfach mal von MySQL aus...
Ein mehrspaltiger Index lohnt sich nur dann, wenn die erste Spalte im Index auch immer im WHERE-Teil zwingend vorkommt - sonst wird der Index nicht benutzt.
Warum kommen die Spalten 7-9 immer automatisch dazu und was sagen sie aus? Wenn die Spalten wirklich so wichtig sind, dann könntest du über diese einen Index machen.
Eine Beispiel-Query wäre imho ganz nützlich.

**web4free** · 30.05.2008, 23:39

Hi,

ja klar hab ich vergessen die wichtigen Sachen wieder zu erwähnen.
PHP und Mysql sind meine 2 Lieblinge.

Es geht um eine KFZ-Plattform
Die Spalten 7-9 beinhalten:

7: Status ob eine Anzeige noch aktiv ist oder nicht und ist vom Typ INT
8: Besagt ob es PKW oder LKW, oder KRAD oder WOMO ist.
9: Beinhaltet eigendlich nur den Ländercode at oder de oder ch

Diese 3 Felder sind unerlässlich und kommen wirklich bei jeden SELECT vor.
Somit wäre hier ein Index über diese 3 Spalten schon mal klug.

Hier mal ein paar Querys wie sie sein könnten:

PHP-Code:


/* Beispiel A */

SELECT

  id, hersteller, modell, weitereFelder

FROM

  tabelle 

WHERE

  spalte2='benzin' AND 

  aktiv=1 AND 

  fart='PKW' AND 

  lcode='at'



/* Beispiel B */

SELECT

  id, hersteller, modell, weitereFelder

FROM

  tabelle 

WHERE

  spalte2='benzin' AND 

  spalte4='blau' AND 

  aktiv=1 AND 

  fart='PKW' AND 

  lcode='at'



/* Beispiel C */

SELECT

  id, hersteller, modell, weitereFelder

FROM

  tabelle 

WHERE

  spalte2='benzin' AND 

  spalte3='fiat' AND 

  spalte4='blau' AND 

  spalte5=25000 AND 

  aktiv=1 AND 

  fart='PKW' AND 

  lcode='at'

Erklärung:
spalte2 = Treibstoff
spalte3 = Hersteller
spalte4 = Farbe
spalte5 = KM

aktiv, fart und lcode sind immer mit dabei.

Und hier würde ich hergehen und einen Index über alle relevanten Spalten legen - würde zumindest logisch klingen.

Ein mehrspaltiger Index lohnt sich nur dann, wenn die erste Spalte im Index auch immer im WHERE-Teil zwingend vorkommt - sonst wird der Index nicht benutzt.

Verstehe ich das richtig dass ich dann die WHERE-Klausel anderst rum aufbauen sollte.
Also zuerst die 3 Spalten die immer kommen und dann erst mit den anderen und alle Spalten im Index so anlegen wie Sie im Query aufgerufen werden.
Aber das mit der Reihenfolge ist ja das was mir nicht eingeht, da die Reihenfolge nie gleich ist wie man aus den 3 Beispielen ja erkennen kann.

**TheFish511** · 31.05.2008, 01:18

Les es dir am besten einmal selber durch:
http://dev.mysql.com/doc/refman/5.1/...n-indexes.html

Die Reihenfolge ist egal, die erste Spalte muss nur zwingend vorkommen. Anhand den Beispielen auf der verlinkten Seite wird das ja recht deutlich.

Ich würde es einfach mal ausprobieren und n bissl benchmarken.

**web4free** · 01.06.2008, 18:59

Hi,

ich hab mir das mal durchgelesen und in die Tat umgesetzt.
Ich habe den Key neu organisiert und jene Spalte die immer mit dabei ist als Erstes angeführt.

Das Ergebnis ist in der Tat um einiges schneller und Werte unter 1 Sekunde sind keine Seltenheit mehr. Ab und zu dass ich bei der Zeitmessung auf 10-14 Sekunden komme, aber ich glaube das hängt dann mit den Inserts zusammen - also wenn gerade ein großer Insert läuft mit 5000-10000 Datensätzen und ich gerade zu dieser Zeit den SELECT mache.
Keys temporär deaktivieren beim Insert würde mir hier beim SELECT aber dann das Genick brechen.

So richtig zufrieden bin ich noch nicht, aber ich habe in der Tat wieder etwas dazugelernt was die Index(e) betrifft.
Was mir halt auch schleierhaft ist - obwohl der Index bei der Kardinalität die selbe Anzahl hat wie der PrimaryKey ist es schneller.
Wo es immer noch schwer hackt ist, wenn ich dann für bestimmte Sachen einen COUNT(*) machen muß. Da schwirrt mir sogar die Kiste ab - drotz des Keys.

**TheFish511** · 01.06.2008, 19:45

Was mir halt auch schleierhaft ist - obwohl der Index bei der Kardinalität die selbe Anzahl hat wie der PrimaryKey ist es schneller.

Dein Primary Key wird bei deinen Abfragen gar nicht genutzt. Lass dir einfach mal eine deiner Queries erklären ( EXPLAIN SELECT ... ). Übrigens verwendet MySQL stets nur einen Index.

Wo es immer noch schwer hackt ist, wenn ich dann für bestimmte Sachen einen COUNT(*) machen muß. Da schwirrt mir sogar die Kiste ab - drotz des Keys.

Beispiel-Query!

**web4free** · 01.06.2008, 20:36

EXPLAIN kenn ich, nutz ich immer und immer wieder um eben schlauer zu werden und es besser als zuvor zu machen.

Heute komm ich nicht mehr dazu Beispiel-Querys zu posten - hole ich aber morgen nach!
Zur Zeit muß ich mich ein bisserl um den Server kümmern...

**web4free** · 25.06.2008, 16:13

Hi Leute,

ich weiß, lange her, der Tag hat leider nur 24 Stunden

Ich hab mich ja wie weiter oben brav an die Vorschläge gehalten.
Durch die Reorganisation des Querys und des Index, gehts wirklich schneller.

Bei der folgenden Sache bin ich noch den Query schuldig, den ich gleich posten will, da dieser immer noch meine Bremse ist.

Zur Erklärung:
a) ich brauche die Gesamtzahl der Treffer
b) Ausgabe der Treffer auf 30 pro Seite limitiert.

Mein Script dazu:

PHP-Code:


// Für die Gesamtanzahl der Treffer:

$SQLcount = mysql_query("SELECT count(id) FROM anzeigen WHERE ".$query2.$sorter);

list($number) = mysql_fetch_row($SQLcount);



// Die Limitierte Ausgabe:

$result = mysql_query("SELECT * FROM anzeigen WHERE ".$query2.$sorter."LIMIT $left_limit, $lines);

while ($row=mysql_fetch_array($result))

{

}

Rufe ich das Script auf, ohne dass ich zuerst alle Treffer suche, gehts unter einer Sekunde.
Mit Gesamttreffer dauert es zwischen 3 und 10 Sekunden.
Das versteh ich nicht.

Klar ist, dass ich beim 2. Query das * noch durch die Spaltennamen ersetzen muß, welche wirklich benötigt werden, damits ein bisserl performanter ist.

**TheFish511** · 25.06.2008, 16:40

PHP-Code:


$SQLcount = mysql_query("SELECT count(id) FROM anzeigen WHERE ".$query2.$sorter);

Der WHERE-Teil besteht aus den Spalten, wo du schon den Index drüber gelegt hast? Zeig am besten mal $query2 und $sorter.

**web4free** · 25.06.2008, 21:10

Hi,

Also die Querys könnten folgendermaßen aussehen:

PHP-Code:


... WHERE aktiv=1 AND fart='PKW' AND (Fahrzeugtyp='Van/Kleinbus');

... WHERE aktiv=1 AND fart='PKW' AND (Fahrzeugtyp='Van/Kleinbus') AND Kilometer < '10000';

... WHERE aktiv=1 AND fart='PKW' AND (Fahrzeugtyp='Van/Kleinbus') AND

Kilometer < '10000' AND (Kraftstoff='Benzin');

Die sind jetzt ohne dem $sorter.
Mit $sorter würde dann noch ein

PHP-Code:


... ORDER BY Datum ASC;

... ORDER BY Erstzulassung ASC;

... ORDER BY Kilometer DESC

INDEX ist folgenderweise aufgebaut - siehe Screen.
Es ist ein Index über mehrer Spalten die in der WHERE-Klausel vorkommen können.
Logisch das dadurch auch die Splaten beinhalind welche vom ORDER verlangt werden - sollte dies wichtig sein.
Einziger Hacken, die Spalten sind nicht alle vom selben Typ.
Bei den CHARS und VARCHARS hab ich zumindes mal gleiche Zeilenlänge eingetragen, da ich hörte dass MYSQ das dann viel lieber hat.

Angehängte Dateien

screen.jpg (55,3 KB, 430x aufgerufen)

**TheFish511** · 25.06.2008, 23:09

PHP-Code:


$SQLcount = mysql_query("SELECT count(id) FROM anzeigen WHERE ".$query2.$sorter);

Dann kannst du dir hier auf jeden Fall das Sortieren sparen. Macht ja überhaupt keinen Sinn. Ich bin mir grad gar nicht sicher(ich glaube es aber), ob MySQL erst sortiert und dann Aggregatsfunktionen macht, aber wenn, dann ist das natürlich ein enormer Performanceverlust.

**web4free** · 26.06.2008, 09:15

Hi,

Original geschrieben von TheFish511

PHP-Code:

$SQLcount = mysql_query("SELECT count(id) FROM anzeigen WHERE ".$query2.$sorter);

Dann kannst du dir hier auf jeden Fall das Sortieren sparen. Macht ja überhaupt keinen Sinn. Ich bin mir grad gar nicht sicher(ich glaube es aber), ob MySQL erst sortiert und dann Aggregatsfunktionen macht, aber wenn, dann ist das natürlich ein enormer Performanceverlust.

Ja klar, das mit dem $sorter beim count(id); ist natürlich Schwachsinn - hätte ich zumindest noch nie gelesen.
Den $sorter hab ich vom Count mal weggenommen - liegt aber dennoch zwischen 3 und 10 Sekunden.

Leider bringt mir phpMyAdmin keine Zeitangabe wenn ich den count absetze.
Wenn ich den normalen Query absetze schreibt mir phpMyadmin eine Zeit von ca. 0,5 - 0,7 Sekunden, was ich glaube immer noch sehr hoch ist.

Über EXPLAIN erhalte ich folgende Ausgabe wie in diesem Screen gezeigt.

EDIT:
Ich hab jetzt mal probiert, die Zeit wie lange das Script braucht herauszukitzeln.

count(id) + normaler SELECT => 4,95 - 10 Sekunden (inakzeptabel)
normaler SELECT => 2,97 Sekunden
count(id) => 6,65 Sekunden

Eine zeitlang hab ich versucht mit dem Parameter SQL_CACHE zu arbeiten hab dann aber stellenweise das wieder entfernt, weil sich die Anzahl der relevanten Daten minütlich ändern können.

count(id) + normaler SELECT mit SQL_CACHE => 0.00845 Sekunden (Aber Hallo) - leider aber erst nach dem 2. Aufruf und ich glaub auch nur dann, wenn die angeforderten Daten in der DB sich bis zu diesem Zeitpunkt nicht geändert haben, was ja aber wie bereits erwähnt minütlich passieren kann.

Angehängte Dateien

screen.jpg (20,8 KB, 252x aufgerufen)

**case** · 26.06.2008, 09:34

Hallo,

also dein Filter-Such-Index dürfte Schwachsinn sein.... Ich habe es zumindest noch nie probiert, wie sich ein Index über 15 Zeilen oder wie viele es auch sind, verhält.

so wie es für mich aussieht kommen nur Fahrt und Fahrzeugtyp IMMER bei deiner Suche vor, oder?

lege den Index mal nur darüber....

Aber bedenke immer: Zu viele Indizes machen die Tabelle auch langsamer!

Ich gehe mal davon aus, du nutzt MyISAM. Diese Datenbankengine optimiert besser, wenn man einen COUNT(*) macht als ein COUNT(id). Das liegt einfach daran, dass die id wirklich erst gezählt werden muss. Für ein COUNT(*) hat MyISAM die Informationen schon gecached.

**web4free** · 26.06.2008, 09:42

Hallo Case,

Also die Spalten aktiv und fart sind wirklich immer dabei zu 100%.
fart heißt wirklich nur fart und nicht fahrt - kommt nicht vom fahren

Alle anderen sind dynamisch, weil man ja nicht wissen kann wonach ein User sucht.
Klar kann ich über jede Möglichkeit einen Index setzen, aber da bin ich schneller weg vom Fenster als ich Hallo sagen kann, weil ich dann für Importe 3 Tage brauche :-)

Nein das mit der Anzahl der Indexe ist mir schon klar.
Genutzt wird MyIsam.
Nur was ist jetzt bei einen COUNT richtig?
Die einen sagen * ist besser, andere wiederum erklären, dass hier die Engine alle Spalten und Rows durchkämmt was Performance frisst.
Zuerst hatte ich ja das * und hab dann in 4 Std. alle * durch nur eine Spalte - meistens id weil Primary - ersetzt.
Was soll ich jetzt glauben?

**case** · 26.06.2008, 09:49

an Gott sollst du glauben

ne, also ich glaube eigentlich auch meißt nur so richtig, was ich selber gebenchmarked habe.... also probiere es einfach aus.... genauso solltest du das mit dem Index machen...

Und du solltest über spalten, die nicht auf jeden Fall vorkommen auch garkeinen Index setzen... Das hast du wohl vorhin ein wenig falsch verstanden

Nur meiner Meinung nach hast du zu große Indizes gesetzt und deshalb wird deine Datenbank ausgebremst.... Versuche mal den Filter-Such-Index zu löschen und dann setzt du einen neuen Index auf fart (und nicht fahrt^^). Dann benchmarkst du, wie lange es mit COUNT(*) und wie lange es mit COUNT(id) dauert. Wenn du davon schonmal das bessere gefunden hast, und dir der query noch zu langsam ist, so kannst du noch ein paar Indizes verändern und kannst dann bei jedem veränderten Index benchmarken, was das jetzt gebracht hat und so kommst du dann zu dem besten Ergebnis.

Index aber richtig gesetzt