BAUSTELLE: Zusätzliche Indizes für die Datenbank

[kopfpilot]

Hallo Miteinander,

auf dem PrimaryKey liegt von Haus aus ein Index. Dieser Sollte im Idealfall die recht effiziente Strukutur des B-Baum verwenden. Besser wären noch Hashtables die aber AFAIR in diesem Bereich nicht verfügbar sein sollten.

Sinn machen Indizes bei Attributen die als Fremdschlüssel-Referenziert werden, sowie Attribute die für eine Where-Klausel oder für ein Join verwendet werden. Sprich alles wonach gesucht oder verglichen werden soll. Das sind in der Regel IDs oder keywords.

Jeder Index verbraucht verhältnissmässig viel Platz, erlaubt aber dank Effizienter Baumstruktur recht rasche Zugriffszeiten. Bei 1024 Elementen müssten in einer verketten Liste schlimmstenfalls 1024 Elemente angeschaut werden... In einem  B-Baum der Ordnung 4 und einer Tiefer von 5 wären das dann schon nur noch 5 Zugriffe (maximal 5-10 mehr)...

Allerdings können Aggregationen wie sum, count oder group by nicht durch Indizes beschleunigt werden.

Anhand der SQL-Statements und der Anzahl der Tuppel der Tabelle lässt sich schon ersehen, ob ein zusätzlicher Index sinn macht!

<Nachtrag> Das sagt MySQL selber dazu: http://dev.mysql.com/doc/refman/5.1/de/mysql-indexes.html

Ein Index wird a) beim Anlegen des Indexes erstellt oder b) bei insert/update eines Daten-Tuppels. Daher ist er immer aktuell und muss nicht von Hand angestoßen werden.

Nachteil: Die Insert- und Update-Operation dauert länger (ist teuerer) als vorher. Allerdings wird sowas höchstens beim Daten import spürbar, wenn diverse Daten inserted oder updated werden...
</Nachtrag>

Gruß
kopfpilot

[DokuMan]

Danke für die Info. Hast du denn auch eine Idee, welche bereits angesprochenen Indices sinnvoll sind und welche vielleicht eher nicht?

[kopfpilot]

Ich habe in meinem Shop mit gut 3500 Artikeln (leider noch nicht online) in folgenden Tabellen zindestens ein paar Hundert Tupel bis hin zu knapp 7000...

products_to_categories(pk:products_id*,categories_id*,index:categories_id)
products_description(pk:products_id,language_id*,index:language_id,products_name*)
products(pk:products_id*,index:products_ean(?),manufacturers_id)
personal_offers_by_customers_status_n(pk:price_id*,index:products_id)
categories_description(pk:categories_id*,language_id*,index:language_id,categories_name*)
categories(pk:categories_id*,index:parent_id*)

Vor der Klammer steht die Tabelle. Der PK ist der Primärschlüßel den die Tabellen aktuell verwenden. Index steht für Attribute die auf den ersten Blick sinn machen.
Die Attribute mit * verfügen bereits über einen Index.

Aus der MySQL-Doku ist zu entnehmen, dass ein zusammengesetzes PK von Links aus "greift".

Als kleines Beispiel:
products_to_categories(pk:products_id*,categories_id*,index:categories_id)

Code: SQL  [Auswählen]

1. SELECT * FROM products_to_categories WHERE products_id=10;
2. SELECT * FROM products_to_categories WHERE products_id=10 AND categories_id=5;
3. SELECT * FROM products_to_categories WHERE categories_id=5;

Der erste und zweite Select verwenden den Index und würden den Zugriff beschleuningen, da die Index-Eintrag von links aus evaluiert werden.

Der dritte Select würde ohne den zusätzlichen Index ins leere fassen, da bei der PK-Eintrag von links aus evaluiert wird und keine entsprechende Information enthält. Wird jetzt der Index auf categories_id gelegt, hätten wir wiederum einen treffer und der Zugriff wird beschleunigt.

Ich würde diese ganzen zusammengessetzen Indizes wirklich nur dann anwenden, wenn sie in der Form auch in einer Where-Klausel vorkommen. Ansonsten würden sie nur helfen, wenn sie als Unique eingesetzt würden und ein Insert verhindern sollen... geschmackssache

Im Grunde lässt sich sehr genau klären wo Indizes sinn machen, indem man jeden SQL-Statment mit EXPLAIN ausführt und sich das Ergebnis anschaut... Anonsten könnte ein beherztes grep auf WHERE/JOIN .. ON helfen um zu schauen worauf jeweil gematched wird.

Gruß
kopfpilot

[kopfpilot]

Dazu habe ich mal per grep ein paar WHERE-Klauseln rausgefischt:

personal_offers_by_customers_status(pk:produts_id*,idx=quantity)  

Code: SQL  [Auswählen]

EXPLAIN SELECT * FROM personal_offers_by_customers_status_1 WHERE products_id=10 AND quantity <5

Dieser Select wird ebenfalls beschleunigt durch den PK-Index, der Index auf quantity bringt keinerlei Beschleunigung und ist überflüssig.

Diese sollten auf jeden Fall einen vorteil bringen:

addressbook(pk:adress_book_id*,idx: customers_id*)
customers(pk:customers_id*,idx:(customers_email_adress,customers_id))
cusomers_info(pk:customers_info_id*)
customers_status(pk:customers_status_id*,language_id*;idx:customers_status_name*)
products_attribut(pk:products_attribut_id*;idx:products_id,options_id,options_values_id)
products_option(pk:products_options_id*,language_id*)
reviews(pk:reviews_id*;idx:customers_id;products_id)
reviews_descript(pk:reviews_id*,language_id*)
order_status(pk:orders_status_id*,language_id*; idx:orders_status_name*)

Diese würde ich lassen wie sie sind:
coutries(pk:coutries_id*,idx:coutries_name*)
table_zones(pk:zone_id*,idx:zone_country_id)

Code: SQL  [Auswählen]

SELECT * FROM categories WHERE categories_id=10

Erzeugt bei mir erstaunlicherweise eine Fehlermeldung, während ein where auf parent_id beschleuningt wird... ist das bei anderen auch so?

gruß
kopfpilot

[kopfpilot]

Ich hab mal die Probe auf's exemple gemacht mit der Tabelle products_to_categories mit PK auf products_id,categories_id.

Code: SQL  [Auswählen]

EXPLAIN SELECT * FROM products_to_categories WHERE products_id=2
EXPLAIN SELECT * FROM products_to_categories WHERE products_id=2 AND categories_id=1344
EXPLAIN SELECT * FROM products_to_categories WHERE categories_id=1344
EXPLAIN SELECT * FROM products_to_categories WHERE categories_id=1344 AND products_id=2

Alle Selects bis auf Nr.3 werden dank Index beschleunigt. Select Nr.3 wird erst beschleunigt sobald ein Index auf categories_id liegt. Daraus lässt sich ableiten, dass die Reihenfolge in der die Attribute eines Indexes festgelegt sind unwichtig ist, solange alle Verwendung finden. Fehl von Links gesehen eines der Attribute (wie in Beispiel 3) dann greift der Index für keines der Attribute.

Zeit zum aufhören *reusper*

[argy17]

Hi. Ich würd meinen Shop auch gern ein wenig schneller machen.
Habe hauptsächlich das Problem, dass der Aufruf der tiefsten Kategorieebene lange Ladezeiten hervorruft.
Kennt jemand von euch das Problem und kann da vielleicht was zu sagen?
Könnte es an der Product_listing hängen?
Alle anderen Seiten gehen relativ zügig auf, nur eben nicht die, wo er dann Artikel anzeigen soll...

[kopfpilot]

Subjektiv habe ich, dass Gefühl das ein Index auf cateogires_id in der Tabelle products_to_categories den Shop "schneller" gemacht hat.

gruß Kopfpilot

[argy17]

kann mir bei meinem problem jemand helfen bzw kennt das jemand und weiß woran es liegen könnte?

[Tomcraft]

Wie viele Produkte sind denn im Shop?

[argy17]

Aktuell etwas über 2200.

[Tomcraft]

Und wie viele Optionen sind angelegt?

[argy17]

wenn du sowas wie verschiedene farben oder so meinst dann sind es glaub ich max. 14 oder so.
Ist aber bei recht wenigen Artikeln so viel.
Bei den meisten sind es wenn dan mal 4 oder 5.
Ist ganz unterschiedlich.

[zub]

ich habe bei goggle mal das gefunden

[ Für Gäste sind keine Dateianhänge sichtbar ]

[argy17]

das kenn ich auch schon. ist halt nur die frage ob das mein problem löst. da der shop so ja nich unbedingt langsam ist, sondern nur wenn man die letzte kategorie aufruft, also da wo dann die artikel angezeigt werden...

[argy17]

also ich hab jetzt schon verschiedene sachen probiert.
boxen auskommentiert, cache an gestellt etc und auch eine andere product_listing.php genommen. Hilft alles nicht wirklich. minimal ist es besser aber so richtig nicht.
hat da jemand nen ultimativen tipp für mich?

EDIT:
product_to_categories mit index versehen brachte einen enormen geschwindigkeitszuwachs...
hat sich also scheinbar erledigt...