MySQL - Tabelle optimieren

Hi,

Folgende Tabelle:

Code:

CREATE TABLE `mautdaten` (
  `Datum` date NOT NULL default '0000-00-00',
  `Zeit` time NOT NULL default '00:00:00',
  `Referenznummer` bigint(12) unsigned zerofill NOT NULL default '000000000000',
  `MautabschnittsID` smallint(4) unsigned zerofill NOT NULL default '0000',
  `Strassenkennzahl` char(5) NOT NULL default '',
  `Mautabschnittsbezeichnung` char(60) NOT NULL default '',
  `KfzKennzeichen` char(12) NOT NULL default '',
  `Nationalitaet` char(2) NOT NULL default '',
  `GoBoxNummer` char(18) NOT NULL default '',
  `KfzKennungFraechter` char(20) NOT NULL default '',
  `KostenstelleFraechter` char(20) NOT NULL default '',
  `Vertragsart` char(4) NOT NULL default '',
  `Kategorie` tinyint(2) unsigned zerofill NOT NULL default '00',
  `Hinweis` char(4) NOT NULL default '',
  `Zahlungsmittel` bigint(20) unsigned zerofill NOT NULL default '00000000000000000000',
  `Netto` int(7) unsigned zerofill NOT NULL default '0000000',
  `USt` int(7) unsigned zerofill NOT NULL default '0000000',
  `Brutto` int(7) unsigned zerofill NOT NULL default '0000000',
  KEY `Datum` (`Datum`),
  KEY `Zeit` (`Zeit`),
  KEY `GoBoxNummer` (`GoBoxNummer`),
  KEY `Netto` (`Netto`),
  FULLTEXT KEY `KfzKennzeichen` (`KfzKennzeichen`)
) TYPE=MyISAM ROW_FORMAT=FIXED

Wie leicht zu erkennen ist, handelt es sich um die Tabelle für Go-Maut Datensätze. Zur Tabelle kommen pro Monat ~20MB an Daten hinzu, sprich jetzt im Mai nach 4 Monaten ~75MB Daten und 15 MB Index.
Da die Spalten Datum, Zeit, GoBoxNummer, Netto und KfzKennzeichen oft in WHERE Klauseln benötigt werden, hab ich diese zum Index hinzugefügt (Ausnahme Spalte Netto, welche nicht in WHERE Klauseln vorkommt, aber ich erhoffte mir durch einen Index eine schnellere Summierung der Werte).


Abfrage #1:

Code:

EXPLAIN
SELECT MAX(datum) AS LetztesDatum,KfzKennzeichen
FROM mautdaten
GROUP BY KfzKennzeichen
ORDER BY NULL;

Ergebnis:

Code:

Using temporary

Hier wird kein Index verwendet


Abfrage #2:

Code:

EXPLAIN
SELECT LPAD(SUM(netto),7,'0') AS Netto,KfzKennzeichen
FROM mautdaten
WHERE datum>='DATUMVON' AND datum<='DATUMBIS'
GROUP BY KfzKennzeichen
ORDER BY NULL;

Ergebnis:

Code:

Using where; Using temporary

Hier wird Datum als Index verwendet

Bevor ich ORDER BY NULL hatte, wurde mir beim explain auch noch zusätzlich using filesort angezeigt. Laut MySQL Handbuch sollte man dies vermeiden, allerdings konnte ich keine Auswirkung (positive und negative) feststellen.

Mein Fragen:
Was kann ich noch beschleunigen? Warum wird bei der ersten Abfrage kein Index/keine Indizes verwendet?
Bedacht werden sollte auch, dass die Tabelle nach einem Jahr ~250 MB haben wird. (Anfang 2005 werd ich die Tabelle mit den Daten von 2004 umbennen und eine neu Tabelle mit den 2005er Daten machen)

Danke für jeden Hinweis

temptables werden auch bei "group by" verwendet.
damit alles schön schnell geht sollte man
*) tmp_table_size sollte groß genug sein
*) der temptable sollte entweder auf einem memdrive oder auf mehreren physischen datenträgern sein

wenn er bei sorts einen temptable erstellt dann sort_buffer und read_rnd_buffer_size erhöhen.

wenn möglich, dann nichts aus dem table löschen, sonst wird der table "fragmentierter" und damit auch langsamer, bis OPTIMIZE TABLE ausgeführt wird.

allgemein zu datenspeicherung von so einem großem table:
wenn möglich dann teilen.. weil ein 4 gig table ist unbearbeitbar und damit meine ich auch von der wartung her. ich denke ich würde einen table machen der die großen datentable pro jahr oder vl besser pro fixer größe enthält und jegliche queries verwaltet. der overhead/query wäre imo ziemlich gering und du müsstest die tabletrennung niemals händisch durchführen. is nur so eine idee

ich nehm mal an du musst nicht nur daten aufnehmen sondern auch queries durch diese daten machen um gewissen informationen, statistiken, usw. herausholen zu können. ein allgemeines abstrahiertes system könnte dir viel arbeit ersparen = eine klasse die einen table mit allen bisher gesamelten tables und deren daten beinhält und queries, egal ob insert, select oder wwi handhabt.

also:

tblDataManager
beinhält tablename und vl auch noch andere wichtige und schnell ereichbare daten. zB daten eingetragen von datum bis datum (= 2 datelines) usw.

dieser table verwaltet also die anderen tables die deine mautdaten beinhalten. das ganze eingepackt in eine schöne klassen die funktionen wie query() usw. hat und entscheidet welche tables von den queries betroffen sind blabla.

ein beispiel:
tblDataManager
1. Datensatz: 'tbl2003', datelinevon (unix timestamp), datelinebis, ..
2. Datensatz: 'tbl2004', usw.

die tables werden natürlich von der klasse DataManager bei bedarf selber erstellt, nach bestimmten regeln.. zB größe des aktuellen tables größer als 500 mb oder anzahl der datensätze größer als 50.000 oder so ähnlich.

ich hoffe ich hab mich jetzt verständlicher ausgedrückt. das system wäre einfach nur g0il, kann aber natürlich sein dass es für deine Aufgabe/Zeitrahmen nicht geeignet ist.

ich muss mich phex bedingungslos anschließen. es stellt sich jedoch die frage, ob du nicht wegen performancegründen auf eine gewisse redundanz zurückgreifen solltest.
beispiel: du nimmst zusätzlich zur spalte "komplettes-kennzeichen" noch eine mit dem bezirk. fixe länge+index verstehen sich von selbst imho.
das würde eben viel sinn machen wenn du sonst oft eine substring-funktion o.ä. brauchst

er meint matphex!