MySQL Tabelle flotter machen

[Medium]

Mahlzeit zusammen. Wir haben bei einem Kunden Drucker an Waagen gegen ein DB-Log getauscht, dass statt auf Papier die Ausgaben der Waagen protokolliert. Das landet alles in einer Tabelle, die mit einem kleinen Tool gefiltert und gedruckt werden kann. Mittlerweile sind dort so ca. 150k Datensätze aufgelaufen, und - nicht ganz unerwartet - wird das ganze langsamer. Leider stärker als erhofft, so dass ich jetzt nach Mitteln suche, meine Tabelle und Abfragen "aufzupeppen".

Die Tabelle:

Code:

CREATE TABLE `log` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `machinenr` int(10) unsigned NOT NULL default '0',
  `printerdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `packagenr` int(10) unsigned NOT NULL default '0',
  `charge` int(10) unsigned NOT NULL default '0',
  `prodname` varchar(255) NOT NULL default '',
  `prodnr` varchar(10) NOT NULL default '',
  `weight` decimal(10,2) NOT NULL default '0.00',
  `rating` char(2) NOT NULL default '',
  `deviation` decimal(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`))
  ENGINE=InnoDB
  DEFAULT CHARSET=latin1;

Die Selects dazu sehen alle in etwa so aus:

Code:

SELECT * FROM log 
WHERE (printerdate BETWEEN :dts AND :dte) 
ORDER BY printerdate, packagenr

Wobei es vier wählbare Sortierungen gibt, und es kann nach einzelnen Werten in "machinenr", "prodname", "prodnr" und "rating" gefiltert werden (entsprechende Anfügung (vorne) von "AND (field=:valueparam)" an den WHERE-Teil).

Die möglichen Sortierungen sind:
1) ORDER BY printerdate, packagenr
2) ORDER BY charge, packagenr, printerdate
3) ORDER BY prodname, printerdate, packagenr
4) ORDER BY prodnr, printerdate, packagenr

Erwarteterweise ist 3) mit Abstand das langsamte, doch auch die anderen sind zuweilen mit 5-10 Sekunden einfach zu lange unterwegs. Mit ist klar, dass die Tabelle alles andere als gut Indexiert ist, allerdings haben meine Versuche mit diversen Indizes auch nicht so viel verbessert - ich steck da einfach nicht tief genug drin.
Leider muss das große Textfeld "prodname" mitgeschlörrt werden, und um ein "SELECT *" komme ich auch nicht herum, da alle Felder in einem Grid angezeigt werden.

Daher meine Frage an die DB-Experten hier: Gibt es zumindest schon mal irgend etwas offensichtliches, womit sich der arme Log-Ausdruck-Mitarbeiter etwas entnerven ließe?

Dankschö schon mal!

[generic]

Schau dir die Ausführungspläne an.
Oft stellt man fest, dass manche Indizes nicht genutzt werden.

In bestimmten Konstellationen kann mit Tabellen Partitionierung gearbeitet werden, um die Geschwindigkeit zu erhöhen.

[Bernhard Geyer]

Etl. einfach auch einfach mal auf eine aktuelle MySQL-Version gehen.

Wie schaut es eigentlich mit dem Speicher aus? Wie viel RAM darf sich den MySQL genehmigen. In den standardinstallation ist das ja teilweise sehr restriktiv so das die DB primär vom erlaubten Speicher ausgebremst wird und keinerlei Infos im RAM halten kann.

[jobo]

Ich seh da gar keine Indizes. Ist das der Auslieferungsstatus?
Wie sieht der Zugriff aus? Muss gefiltert werden? Oder kann man auch einfach nur munter 150k Datensätze sortieren lassen?

Mein Vorgehen wäre:
1. alle der filterbaren (4?) Felder einzeln indizieren.
2. anzunehmen, dass kein Mensch mehr als 1000 Datensätze durchlesen möchte
3. Die Sortierung auf einem Ergebnisset <1000 erst mal vernachlässigen (dürfte da tatsächlich keine Rolle spielen)

weiterhin
Die Anwender evtl zur Einschränkung der Suchergebnisse "zwingen" > genauerer Filter

Analyse per Ausführungsplan lässt sich auch nur schwer ablehnen.

Achso: Filter nur per Gleichheit? Oder auch Like, ...?

[Medium]

@generic: Wie komme ich an die Pläne, und wie kann ich die Nutzung meiner Indizes dann im Zweifelsfall gewährleisten? (Ich bräuchte ja vor allem erst mal sinnvolle Indizes )

Die Version ist so eine Sache. Der PC incl. MySQL ist vom Kunden beigestellt, das wird eher nicht so einfach möglich sein.
Speicher:
Der PC hat 2GB
MySQL
Key Buffer 2MB
Sort buffer 212kB
InnoDB
Buffer pool size 11MB
Additional mem Pool size 2MB

Bevor ich aber am Server selbst rumdrehe (bzw. das veranlassen muss), glaube ich, dass da Potenzial in Tabelle und Abfrage stecken muss - die sind nämlich damals noch recht naiv entstanden, als erste Gehversuche nach Umstellung von Paradox auf MySQL als Standard-DB hier im Betrieb. Zumindest hoffe ich, dass der aktuelle Zustand hochgradig unoptimal ist

[DeddyH]

EXPLAIN sollte eine Reihe von Informationen liefern.

[Medium]

Huch, rotes Kästchen

Zitat von jobo:

Ich seh da gar keine Indizes. Ist das der Auslieferungsstatus?

Das ist der Ist-Zustand, ja

Zitat:

Wie sieht der Zugriff aus? Muss gefiltert werden? Oder kann man auch einfach nur munter 150k Datensätze sortieren lassen?

Es wird standardmäßig auf einen Zeitraum von einm Monat gefiltert, der Anwender kann aber beliebige Zeiträume und Einschränkungen wie oben genannt machen.

Zitat:

1. alle der filterbaren (4?) Felder einzeln indizieren.
2. anzunehmen, dass kein Mensch mehr als 1000 Datensätze durchlesen möchte
3. Die Sortierung auf einem Ergebnisset <1000 erst mal vernachlässigen (dürfte da tatsächlich keine Rolle spielen)

1. Das war einer meiner Versuche, aber leider ohne merklichen Erfolg
2. Es kann sein, dass Listen gedruckt werden sollen, die so eine Länge haben, doch
3. Inwiefern?

Zitat:

Die Anwender evtl zur Einschränkung der Suchergebnisse "zwingen" > genauerer Filter

Da müsste ich zunächst klären, ob das eine Option ist. Es werden Prinzipiell auch Gesamtlisten gebraucht, mal hören.

Zitat:

Achso: Filter nur per Gleichheit? Oder auch Like, ...?

Nur Gleichheit.

Danke dir!

[Medium]

Zitat von DeddyH:

EXPLAIN sollte eine Reihe von Informationen liefern.

Code:

EXPLAIN SELECT * FROM log
WHERE (printerdate BETWEEN '01-01-2011 00:00:00' AND '01-02-2011 00:00:00')
ORDER BY printerdate, packagenr

Liefert:
select_type = SIMPLE
table = log
type = ALL
possible_keys = null
key = null
key_len = null
ref = null
rows = 167389 (das sind alle)
Extra = Using where; Using filesort

Leider sagt mir das nicht viel, ausser dass mir "filesort" irgendwie doof klingt.

[DeddyH]

Dann setz doch einmal einen Index auf printerdate und wiederhole das Ganze noch einmal. Zumindest die Testabfrage sollte dann schon etwas schneller Ergebnisse liefern, sofern nicht wieder null unter possible_keys gemeldet wird.

[Medium]

Der Key wird, laut explain, dann durchaus verwendet, aber wird es subjektiv entweder kaum oder garnicht schneller, wenn ich die Abfrage abschicke
(Ich mach das via PC-Anywhere auf dem Server beim Kunden direkt, so dass Netzwerklaufzeiten keine Rolle spielen dürften.)
Da war meine Hoffnung auf ein "Wah! Stell einfach mal das und jenes um, und es fluppst - sowas weiss man doch!" leider zu optimistisch hm?