Welchen Index zur Datenbankbeschleunigung?

[Ralf]

Hi,

ich habe eine MySQL DB mit einer Tabelle mit den Spalten:
a(bigint)
b(varchar)
c(float)
d(float)
e(float)
f(float)

Die Tabelle hat ca. 4 Mio Datensätze.

Jetzt mache ich fast ausschlieslich Abfragen der Form:

  
SELECT a, c, d, e, f FROM meineTabelle WHERE b='foo' AND a >= 1262300400 AND a<= 1270072799 ORDER BY a

So eine Abfrage dauert ohen index auf meinem Rechner ca. 30 sek.
Wie erzeuge ich einen Index um diese Art Abfragen max. zu beschleunigen?

Gruß
Ralf

[ChrisB]

Hi,

Jetzt mache ich fast ausschlieslich Abfragen der Form:

SELECT a, c, d, e, f FROM meineTabelle WHERE b='foo' AND a >= 1262300400 AND a<= 1270072799 ORDER BY a

>   
> So eine Abfrage dauert ohen index auf meinem Rechner ca. 30 sek.  
> Wie erzeuge ich einen Index um diese Art Abfragen max. zu beschleunigen?  
  
<http://dev.mysql.com/doc/refman/5.1/en/create-index.html>  
  
Auf a und b einen zu setzen, bietet sich bei der genannten Query wahrscheinlich an.  
  
Btw., der Wert von a sieht nach einem Unix Timestamp aus.  
Es ist fast immer zu bevorzugen, einen der Datumstypen der Datenbank stattdessen zu verwenden.  
  
MfG ChrisB  
  

-- 
RGB is totally confusing - I mean, at least #C0FFEE should be brown, right?

[Ralf]

Auf a und b einen zu setzen, bietet sich bei der genannten Query wahrscheinlich an.

Dachte ich auch uns habe:

  
CREATE INDEX ab ON meineTabelle (a, b);  

Das hat aber fast keine Beschleunigung gebarcht :-/ Braucht auf demgeliche Rechner jetzt statt 28-30 sek. 22-24. Ist zwar in Sekunden nicht ríchtig zu beurteilen, da das OS ja ab und zu noch irgendetwas macht aber weit jensets dessen was ich erwartet habe. Der Index wurde auch wirklich angelegt, habs überprüft.

Btw., der Wert von a sieht nach einem Unix Timestamp aus.

Ja.

Es ist fast immer zu bevorzugen, einen der Datumstypen der Datenbank stattdessen zu verwenden.

Habe das jetzt in IMESTAMP geändert, Erfolg marginal.

[ChrisB]

Hi,

Auf a und b einen zu setzen, bietet sich bei der genannten Query wahrscheinlich an.

Dachte ich auch uns habe:
CREATE INDEX ab ON meineTabelle (a, b);

Du fragst die Spalten einzeln ab, also solltest du auch auf jede einzeln einen Index setzen, nicht auf beide zusammen.

MfG ChrisB

[dedlfix]

Hi!

Du fragst die Spalten einzeln ab, also solltest du auch auf jede einzeln einen Index setzen, nicht auf beide zusammen.

Und dass MySQL die Indexe auch nimmt, sollte man nicht nur anhand der gefühlten Beschleunigung annehmen, sondern mit EXPLAIN nachprüfen.

Lo!

[Ralf]

Und dass MySQL die Indexe auch nimmt, sollte man nicht nur anhand der gefühlten Beschleunigung annehmen, sondern mit EXPLAIN nachprüfen.

Ooops! Explain sagt, er nimmt den Index gar nicht:

  
Using WHERE, Using filesort  

Auch ein USE INDEX(meinIndex) ändert daran nichts?

Ralf

[Cheatah]

Hi,

Du fragst die Spalten einzeln ab, also solltest du auch auf jede einzeln einen Index setzen, nicht auf beide zusammen.

MySQL verwendet mehr als einen Index pro Tabelle? Krass. Kannst Du eine Erklärung verlinken, wie sie das performant hinbekommen?

Cheatah

[Sven Rautenberg]

Moin!

Auf a und b einen zu setzen, bietet sich bei der genannten Query wahrscheinlich an.

Dachte ich auch uns habe:
CREATE INDEX ab ON meineTabelle (a, b);

Du fragst die Spalten einzeln ab, also solltest du auch auf jede einzeln einen Index setzen, nicht auf beide zusammen.

Nein, das bringt nichts. MySQL nutzt nur genau einen Index pro Tabelle - und der Optimizer wählt unter den verfügbaren Indices einen "geeigneten" aus. Das ist im Idealfall der Index, der am stärksten die Datenmenge einschränkt.

Wenn du also zwei Indices auf die Spalten a und b erzeugst, wird in einer Abfrage, die die Spalten a UND b benutzt, nur einer der beiden Indices benutzt, und damit Potential verschenkt - insbesondere wenn diese beiden Indices für sich genommen nicht sonderlich gut einschränken.

Ein kombinierter Index hängt die mehreren Spalten (hier also a und b) in der gewünschten Reihenfolge aneinander und greift immer, wenn der Optimizer erkennt, dass 1) alle Spalten des Index abgefragt werden, oder 2) alle Spalten von der ersten Indexspalte aufsteigend abgefragt werden.

Wenn es also aufgrund der Gesamtmenge an Querys sinnvoll ist, auf beide Spalten einen Index zu setzen, und zusätzlich auch auf die Kombination beider Spalten, dann würde man 1) einen Index auf die Kombination von Spalte a und b setzen (das wird dann für Querys auf a allein sowie auf a + b genutzt), sowie zusätzlich noch auf Spalte b allein.

Allerdings hilft ein Index nur bedingt. Wenn die abgefragte Datenmenge so groß ist, dass auch nach der Konsultation des Index das Ergebnis nicht in den RAM passen würde, muss MySQL trotzdem auf der Festplatte in eine temporäre Tabelle kopieren. Das kostet extrem viel Zeit.

- Sven Rautenberg

[dedlfix]

Hi!

MySQL nutzt nur genau einen Index pro Tabelle - und der Optimizer wählt unter den verfügbaren Indices einen "geeigneten" aus. Das ist im Idealfall der Index, der am stärksten die Datenmenge einschränkt.

Das kann ich nicht bestätigen. Ich habe da eine Tabelle mit 14000 Datensätzen und unter anderem zwei Indexen mit je einer Kardinalität von 31 und circa 4700. Diese Abfrage

EXPLAIN SELECT * FROM Transaction WHERE ID\_Account=31 AND ID\_Payee=96

bringt mir dieses Ergebnis:

id  select_type  table       type        possible_keys       key                 key_len  ref  rows  Extra
1   SIMPLE       Transaction index_merge ID_Account,ID_Payee ID_Payee,ID_Account 5,5      NULL 73    Using intersect(ID_Payee,ID_Account); Using where

Daraus entnehme ich, dass MySQL durchaus auch zwei Indexe verwendet. Das Handbuch spricht auch von "normally" nicht von "always" (zweiter Listenpunkt):

If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).

Lo!

[dedlfix]

Hi!

MySQL nutzt nur genau einen Index pro Tabelle
Das kann ich nicht bestätigen.

Beim Suchen von Beispielen mit öffentlich zugänglichen Daten, ist es mir meist nicht gelungen, den Fall nachzustellen. Aber dann fand ich die FlightStats. Die sind allerdings recht groß (1,3 GB komprimierter Download - 3,6 GB ausgepackt) und downloaden sich auch recht langsam (60 Minuten bei mir). Zudem sind es kopierte Tabellen und kein SQL-Export, müssen also zu Fuß ins MySQL-datadir kopiert werden, wozu man eine administrative Berechtigung auf dem System benötigt. Nach dem Kopieren und dem gegebenenfalls notwendigen Anpassen der Besitzverhältnisse kann MySQL (5.0 in meinem Fall) aber ohne weiteres Zutun darauf zugreifen. Das Merge- und Session-Script der Download-Seite braucht man nicht.

Man schaue sich die Struktur einer beliebigen ontime-Tabelle an, zum Beispiel ontime_2000_01 und sieht dort einzelne Indexe auf carrier und destination. Damit kann man so eine Abfrage erstellen

EXPLAIN SELECT * FROM ontime\_2000\_01 WHERE carrier = 'AA' AND destination = 'JFK'

und bekommt mit possible_keys: carrier,destination und key: destination,carrier zwei verwendete Indexe zu sehen.

Es kann natürlich auch sein, dass ich das falsch interpretiere, aber ich denke, dass nach Anwenden des ersten Index noch genügend Datensätze zur Auswahl stehen müssen, damit sich die Verwendung des zweiten Indexes lohnt (destination JFK: 3597, carrier AA: 61666, AA+JFK: 958 Datensätze). Schränkt man das ganze noch auf tail_num ein (da liegt auch ein Index drauf) und selektiert

EXPLAIN SELECT * FROM ontime\_2000\_01 WHERE carrier = 'AA' AND destination = 'JFK' AND tail\_num = 'N061AA'

dann wird nur der tail_num-Index verwendet (possible_keys: carrier,destination,tail_num und key: tail_num), denn diese bringt es auf insgesamt nur 51 Einträge in der gesamten Tabelle.

Lo!

[Cheatah]

Hi,

Auf a und b einen zu setzen, bietet sich bei der genannten Query wahrscheinlich an.

für mich sieht b beschränkender aus. Vermutlich ist es sinnvoller, einen Index auf b und a zu setzen als umgekehrt.

Cheatah