MySQL -> Index erstellen

[nina]

hi!
hab ne anfrage, die leider immer ca 10 sec benötigt, bis sie verarbeitet worden ist :(
nun fragte ich mich, ob es sinn machen würde, die tabelle mit einem weiteren index auszustatten. einen volltext index hat sie bereits. dieser bezieht sich auf produktbezeichnung und beschreibung und wird verwendet, wenn eine produktsuche ausgeführt wird.
doch z.b. für das sitemap wird ein query wie dieser verwendet:
SELECT kategorie FROM tab_shop_kategorien WHERE (shop="shop1" AND anzahl>0) LIMIT 0,500
sollte ich hierfür z.b. einen index für shop und einen für anzahl oder einen gemeinsamen erstellen oder was würde sinn machen?
danke
ps: die tabelle ist ca 800mb groß, daher die relativ lange ausführungsdauer, die einen solchen schritt erforderlich macht...

[Cheatah]

Hi,

SELECT kategorie FROM tab_shop_kategorien

was bitte ist ein Tab-Shop?

sollte ich hierfür z.b. einen index für shop und einen für anzahl oder einen gemeinsamen erstellen oder was würde sinn machen?

Die Datenbank verwendet pro Tabelle maximal einen Index. Damit sollte sich Deine Frage beantworten lassen.

Cheatah

[nina]

Hi,

SELECT kategorie FROM tab_shop_kategorien

was bitte ist ein Tab-Shop?

nein!
"tab" wie Tabelle

sollte ich hierfür z.b. einen index für shop und einen für anzahl oder einen gemeinsamen erstellen oder was würde sinn machen?

Die Datenbank verwendet pro Tabelle maximal einen Index. Damit sollte sich Deine Frage beantworten lassen.

:(
Wie soll ich dieses Problem dann lösen?
Es wird ein Index für die Produktsuche benötigt, der sich auf produktdaten (bezeichnung, beschreibung) bezieht.
Zudem sollen auch andere zugriffe zügig erfolgen wie beispielsweise der genannte query.
bitte um weitere tipps.
danke

[Cheatah]

Hi,

"tab" wie Tabelle

warum bringst Du im Namen der Tabelle eine Information unter, die schon dadurch bekannt ist, dass es sich um eine Tabelle handelt, Name_nina?

Die Datenbank verwendet pro Tabelle maximal einen Index. Damit sollte sich Deine Frage beantworten lassen.
Wie soll ich dieses Problem dann lösen?
Es wird ein Index für die Produktsuche benötigt, der sich auf produktdaten (bezeichnung, beschreibung) bezieht.

Handelt es sich bei dem Statement um eine Produktsuche? Wenn nicht, wird ein hierzu nützlicher Index auch nicht verwendet. Somit liegt kein Problem vor.

Cheatah

[nina]

Handelt es sich bei dem Statement um eine Produktsuche? Wenn nicht, wird ein hierzu nützlicher Index auch nicht verwendet. Somit liegt kein Problem vor.

hm...
also wird eine produktsuche ausgeführt, so mache ich das mit MATCH und greife auf den fulltext-index zu. hierbei wird der index sicher genutzt und liefert auch ergebnisse in max 2 sec.
doch im shop haben die user auch neben einer gezielten produktsuche die möglichkeit, in den kategorien zu stöbern. doch hierbei wird in dem where klausel das feld kategorie und anzahl verwendet. somit bringt der index wieder nichts und der user muss warten. bis zu 30sec wenns blöd geht und viele artikel drin sind.
was kann ich tun?

[Cheatah]

Hi,

also wird eine produktsuche ausgeführt, so mache ich das mit MATCH und greife auf den fulltext-index zu. hierbei wird der index sicher genutzt

der, den Du schon hast, nicht der, den Du erstellen möchtest.

doch im shop haben die user auch neben einer gezielten produktsuche die möglichkeit, in den kategorien zu stöbern. doch hierbei wird in dem where klausel das feld kategorie und anzahl verwendet.

_Zusätzlich_ zur Volltext-Suche?

was kann ich tun?

Dir bei jedem Statement überlegen, wie pro abgefragter Tabelle der günstigste Index lauten muss.

Cheatah

[nina]

Dir bei jedem Statement überlegen, wie pro abgefragter Tabelle der günstigste Index lauten muss.

achso. dann habe ich Dich anfangs missverstanden, also Du sagtest, es würde nur 1 index verwendet. das war wohl nur auf ein statement bezogen. hab das so interpretiert, dass nur 1 index pro tabelle sinn macht...
also lasse ich den fulltextindex bestehen und füge einen neuen hinzu.
dann kommt nun die nächste frage: einer oder mehrere.
ich könnte nun einen gemeinsamen für die felder "anzahl" und "kategorie" verwenden, da diese oftmals zusammen in einem WHERE klausel abgefragt werden.
doch manchmal werden auch die felder "shop" und "anzahl" gemeinsam als bedingung gestellt.
in einem andren fall "shop" und "kategorie".
soll ich nun 3 indizes erstellen, die jeweils beide betroffenen felder beinhalten
oder soll ein einzelner index für jedes dieser felder verwendet werden,
wobei das deinem hinweis widerspricht, dass pro abfrage nur auf einen index zugegriffen wird...
???

[wahsaga]

hi,

achso. dann habe ich Dich anfangs missverstanden, also Du sagtest, es würde nur 1 index verwendet. das war wohl nur auf ein statement bezogen.

Genau.

dann kommt nun die nächste frage: einer oder mehrere.

Kommt drauf an :-)

ich könnte nun einen gemeinsamen für die felder "anzahl" und "kategorie" verwenden, da diese oftmals zusammen in einem WHERE klausel abgefragt werden.
doch manchmal werden auch die felder "shop" und "anzahl" gemeinsam als bedingung gestellt.
in einem andren fall "shop" und "kategorie".
soll ich nun 3 indizes erstellen, die jeweils beide betroffenen felder beinhalten
oder soll ein einzelner index für jedes dieser felder verwendet werden,
wobei das deinem hinweis widerspricht, dass pro abfrage nur auf einen index zugegriffen wird...

Wenn du einen Index über x Felder anlegst, kann dieser nur in einer Abfrage genutzt werden, deren Bedingung auch diese x Felder mit einbezieht.

Legst du nur einzelne Indexe für die jeweiligen Spalten an, kann immer nur einer von diesen für die Optimierung der Abfrage verwendet werden.

Du kannst natürlich sowohl einzelne als auch den kombinierten Index anlegen - behalte dabei aber im Auge, dass ein Index die Performance einer Abfrage zwar erhöht, die von INSERT/UPDATE aber verringert - weil dabei der Index (oder die) ja immer aktualisiert werden müssen.

Noch ein Tipp: Mit EXPLAIN kannst du auch analysieren, welchen INDEX eine Query nutzen kann.

gruß,
wahsaga

[Ilja]

yo,

Wenn du einen Index über x Felder anlegst, kann dieser nur in einer Abfrage genutzt werden, deren Bedingung auch diese x Felder mit einbezieht.

nein, die nutzung eines index wird von "links nach rechts" benutzt. es müssen nicht alle x felder in der bedingung vorkommen, solange in den index spalten "linksseitig keine unterbrechung" vorkommt. mit anderen worten ein index über spalte1, spalte2 und spalte3 kann sehr wohl benutzt werden, wenn nur spalte1 angesprochen wird oder aber spalte1 und spalte2

Ilja

[Cheatah]

Hi,

achso. dann habe ich Dich anfangs missverstanden, also Du sagtest, es würde nur 1 index verwendet. das war wohl nur auf ein statement bezogen.

richtig, war es.

hab das so interpretiert, dass nur 1 index pro tabelle sinn macht...

Im Nachhinein: Stimmt, kann man so interpretieren. Für mich war klar, dass wenn dem so wäre die Datenbank selbst einen zweiten Index schon beim Anlegen ablehnen würde. Hätte ich dazu sagen sollen, sorry.

dann kommt nun die nächste frage: einer oder mehrere.
ich könnte nun einen gemeinsamen für die felder "anzahl" und "kategorie" verwenden, da diese oftmals zusammen in einem WHERE klausel abgefragt werden.
doch manchmal werden auch die felder "shop" und "anzahl" gemeinsam als bedingung gestellt.
in einem andren fall "shop" und "kategorie".
soll ich nun 3 indizes erstellen, die jeweils beide betroffenen felder beinhalten
oder soll ein einzelner index für jedes dieser felder verwendet werden,
wobei das deinem hinweis widerspricht, dass pro abfrage nur auf einen index zugegriffen wird...

Eben. Es wird bei jedem Statement pro abgefragter Tabelle maximal ein Index ausgesucht. Wenn Du also nicht zufällig per Self-Join die Tabelle mehrmals abfragst, musst Du Dich jeweils auf einen Index beschränken. Wie viele andere Indexe noch existieren, ist hierbei unerheblich - sie verbrauchen nur beim Ändern der Daten (UPDATE, INSERT, DELETE) Zeit.

Cheatah

[Ilja]

yo,

Eben. Es wird bei jedem Statement pro abgefragter Tabelle maximal ein Index ausgesucht.

ich weiss, wir hatten das thema schon und ich muss gestehen, ich habe leider noch keinen link gefunden, der das bestätig oder dementiert. und zur zeit habe ich auch keine oracle installation, um es zuhause auszuprobieren. aber meiner meinung nach gilt das nur bei AND verknüpfunge in der WHERE klausel.

was spricht den dagegen, dass ein dbms bei einer OR verknüpfung mehrere indexe benutzet ? wäre das nämlich der fall, dass nur ein index dafür benutzt wird, müsste das dbms einen fullscan ausführen.

Ilja

[Cruz]

Hallo,

Eben. Es wird bei jedem Statement pro abgefragter Tabelle maximal ein Index ausgesucht.

Man kann aber im Statement festlegen welchen Index genau man haben möchte, falls einem derjenige Index nicht gefällt, den mysql automatisch für die Anfrage heranzieht.

dann kommt nun die nächste frage: einer oder mehrere.
ich könnte nun einen gemeinsamen für die felder "anzahl" und "kategorie" verwenden, da diese oftmals zusammen in einem WHERE klausel abgefragt werden.

Gemeinsamer Index für gemeinsam abgefragte Felder ist meier Erfahrung nach besser.

Mit EXPLAIN SELECT kann man sehr gut die Ausführung der Statements analysieren und tunen.

http://dev.mysql.com/doc/refman/5.1/en/explain.html

Gruß,
Cruz

[dedlfix]

echo $begrüßung;

Eben. Es wird bei jedem Statement pro abgefragter Tabelle maximal ein Index ausgesucht.

Man kann aber im Statement festlegen welchen Index genau man haben möchte, falls einem derjenige Index nicht gefällt, den mysql automatisch für die Anfrage heranzieht.

Wenn du auch noch geschrieben hättest, wie das geht oder wo genau man sich darüber informieren kann, wäre deine Antwort noch hilfreicher gewesen. Ich hole das mal nach:

... FROM tbl_name [{USE|IGNORE|FORCE} INDEX (key_list)]

Das ist die passende Stelle. Weitere Informationen unter SELECT Syntax und JOIN Syntax.

echo "$verabschiedung $name";