Select-Statement optimieren

[IchHabeEineFr]

Hallo,

habe mal ne Frage an die Datenbank-Spezis:

Ich habe ein Query auf einer Datenbank (MySQL) welches ich gerne optimieren würde (Tabellen-Struktur kann ich nicht ändern).

select * from [table] where [field1] in ('wert1', 'wert2', '....', 'wert20')  AND [field2] > 'Datum'  order by [field3] ASC, [field4] ASC, [field5] DESC, [field6] DESC limit 10;
field1 = Varchar(25)
field2 = Datetime
field3 = Varchar(25)
field4 = Varchar(25)
field5 = Varchar(25)
field6 = timestamp

Die Tabelle hat > 2 Mllionen Datensätze und das Ganze dauert ewig. Ich habe jetzt auf field1 einen Index gelegt, dadurch wurde es etwas schneller, aber nicht schnell genug. Gibt es sonst etwas was ich tun kann um so ein Statement zu optmieren. Indexe auf field2 oder die "order"-Fields brachten nicht.

 

[c_sidi90]

Mehr als 2 Mill Datensätze in ein ResultSet, da wirste mit Optimierung deines Statements auch nicht auf wesentlich mehr Geschwindigkeit kommen. Sinnvoller ist da, dir ein Konzept zu überlegen, wie und WANN du die Daten lädst. Stichwort "Lazyloading". Wenn du zB Daten in einer Tabelle darstellen möchtest, macht es ja auch keinen Sinn alle aufeinmal darzustellen, was soll ein User durch eine 2Millionen große Tabelle scrollen.

 

[tfa]

Wenn die Tabelle so groß oder das Statement so aufwendig ist, dass die DB erstmal Minuten braucht, bis ein Cursor da ist, über den du die Daten lesen kannst, nützt dir Lazy Loading auch nichts. Egal ob da jetzt 2 Millionen oder 20 Zeilen bei raus kommen. Und diese Query ist ja schon auf 10 begrenzt.

 

[IchHabeEineFr]

Wie tfa schon sagt, nutze ich breits "pagging" via limit, das Problem ist einfach die viel "String" vergleiche in der DB (zum Suchen und Sortieren), denke ich.

 

[Tomate_Salat]

Habe noch nicht viel mit DB-Optimierung gemacht, aber ich würde hier einen Index auf [field2,field1] (also einen Index auf beide in dieser Reihenfolge) setzen.

 

[Gast2]

Da hilft nur sich den Explain Plan anzusehen und dann zu sehn was so lange braucht.

 

[c_sidi90]

Hast du den Sort-Buffer mal erhöht? Das kann sich auch positiv in der Geschwindigkeit bei der Sortierung bemerkbar machen.

set sort_buffer_size=xxxxxx;

 

[IchHabeEineFr]

Der Key ueber field1 und field2 scheint zu funktionieren, auch wenn ich nicht weiß wie er bei einem "Wert > Datum" irgendeinen Key bildet.

Bekomme ich irgendwie mehr Infos über Statements bei MySQL Explain zeigt mir nur an welche Keys verwendet werden.

+----+-------------+--------+-------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+--------+-------+---------------+------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | [table]| range | [Key1_field1],[Key2_field1_field2]        | E    | 28      | NULL | 1676284 |   100.00 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+---------+----------+-----------------------------+

 

[parabool]

Wenn viele identische Werte in der indizierten Spalte existieren, schaltet Mysql auf Tablescan um (abhänging davin wie max-seeks-for-key eingestellt ist).
In dem Fall kann man mittels FORCE INDEX die Verwendung des Index erzwingen.

Siehe MySQL :: MySQL 5.1 Referenzhandbuch :: 7.2.15 Vermeidung von Tabellenscans

 

[IchHabeEineFr]

Tablescan wird wohl nur verwendet, wenn in der Spalte type=all (im Explain-Statement) steht. Bei mir steht aber range, weil ich mit limit arbeite. Aber ich schau es mir nochmal näher an, danke.

 

[ign0rant]

Versuch mal

WHERE (field1 = 'wert1' OR field1 = 'wert2' ... OR field1 = 'wert20')

anstatt

WHERE [field1] IN ('wert1', 'wert2', '....', 'wert20')

 

[IchHabeEineFr]

Das hatte ich auch schon versucht, wird von MySQL aber gleich schnell/langsam behandelt. Ich vermute mal das "IN" intern wie "OR" behandelt wird.

 

[Lumaraf]

Stimmt es das die WHERE Bedingung auf die mehrheit der vorhandenen Datensätze zutrifft?
In dem Fall wird man mit Indizes auf field1/field2 oder änderungen an der Bedingung keinen Nenneswerten verbesserungen bekommen können. Dem ORDER BY ist leider auch nicht ohne weiteres mit einem Index zu helfen da hier gemischte Reihenfolgen vorkommen (field3 und field4 aufsteigend, field5 und field6 absteigend). Außerdem war da glaube ich noch die Einschränkung das ein Index nur für die Sortierung verwendet wird wenn alle Spalten in der gleiche Reihenfolge im ORDER BY stehen.

Versuch mal folgendes:
1. leg einen zweispaltigen Index auf field3 und field4 an
2. probier mal diese Query aus
SELECT * FROM

,(SELECT field3,field4 FROM

WHERE [field1] IN ('wert1', 'wert2', '....', 'wert20')
AND [field2] > 'Datum'
ORDER BY [field3] ASC, [field4] ASC
LIMIT 1,9) AS nested
WHERE

.field1 IN ('wert1', 'wert2', '....', 'wert20')
AND

.field2 > 'Datum'
AND (

.field3,

.field4)<=(nested.field3,nested.field4)
ORDER BY [field3] ASC, [field4] ASC, [field5] DESC, [field6] DESC
LIMIT 10;

Keine Ahnung obs wirklich funktioniert, hab mich zuletzt vor 3 Jahren intensiver mit MySQL beschäftigt.

[EDIT]Hatte in der äußeren Query einen falschen Operator, muß natürlich <= sein [/EDIT]

 

[IchHabeEineFr]

danke, habs probiert. Brachte leider keinen Geschwindigkeitsvorteil.

 

[Lumaraf]

danke, habs probiert. Brachte leider keinen Geschwindigkeitsvorteil.

Ah ich hab nach etwas herumtesten eine geringfügige Variation meiner Query gefunden die zumindestens auf meinen Testdaten schneller lief.

SELECT * FROM

,(SELECT field3,field4 FROM

WHERE [field1] IN ('wert1', 'wert2', '....', 'wert20')
AND [field2] > 'Datum'
ORDER BY [field3] ASC, [field4] ASC
LIMIT 10 ) AS nested
WHERE

.field1 IN ('wert1', 'wert2', '....', 'wert20')
AND

.field2 > 'Datum'
AND (

.field3,

.field4) = (nested.field3,nested.field4)
ORDER BY [field3] ASC, [field4] ASC, [field5] DESC, [field6] DESC
LIMIT 10;