Tabellen für viele kleine Datensätze optimieren

[Sir Rufo]

Wofür benötigst du denn `id` int(10) unsigned NOT NULL auto_increment, ?

Eigentlich ist die doch für nix gut ...

• Ein PK würde sich anbieten auf valueID und vdate.
• Statt InnoDB eher MyISAM nehmen.
• Partitions auf die Jahre legen (eine monatliche Unterteilung geht auch mit Extract( YEAR_MONTH FROM vdate ) )

SQL-Code:

CREATE TABLE `history` (
  `valueID` smallint(5) unsigned NOT NULL,
  `vdate` datetime NOT NULL,
  `maxValue` float NOT NULL,
  `meanValue` float NOT NULL,
  PRIMARY KEY (`valueID`,`vdate`)
) 
PARTITION BY RANGE(YEAR(valueDate)) 
PARTITIONS 3 (
  PARTITION `p2012` VALUES LESS THAN (2013) ENGINE=MyISAM , 
  PARTITION `p2013` VALUES LESS THAN (2014) ENGINE=MyISAM , 
  PARTITION `pCurrent` VALUES LESS THAN (MAXVALUE) ENGINE=MyISAM ) 
ENGINE=MyISAM

Um für das nächste Jahr eine neue Partition anzufügen einfach diesen Befehl ausführen

SQL-Code:

ALTER TABLE `values` REORGANIZE PARTITION pCurrent INTO
( PARTITION p2014 VALUES LESS THAN (2015),
   PARTITION pCurrent VALUES LESS THAN MAXVALUE )

Mit den Partitions hat sich dann das Problem mit der Datensicherung auch erledigt, denn nun gibt es pro Partition separate Dateien und wenn nur aktuelle Datensätze angefügt werden, dann bleiben die alten Dateien unberührt und müssen eben nicht mehr gesichert werden.