Search code examples
phpmysqlinnodbauto-increment

MYSQL Reset Auto Increment improve performance

I have a question about MYSQL databases will resetting the primary key improve performance? I use auto increment as key (NumID) see below but the count is in the 42 million mark now and lately I notice up and down performance. The database size is 8GB and there is 450,000 rows at present. I do regularly dump large sections of this database (old dates).

CREATE TABLE IF NOT EXISTS `Count1` (
  `NumID` int(11) NOT NULL AUTO_INCREMENT,
  `MyUser` varchar(100) DEFAULT NULL,
  `MYitID` varchar(100) DEFAULT NULL,
  `APICalls` int(15) DEFAULT NULL,
  `ResultID` longtext,
  `ResultIDtotal` varchar(100) DEFAULT NULL,
  `Time` varchar(20) DEFAULT NULL,
  `iTime` varchar(50) DEFAULT NULL,
  `Date` varchar(30) DEFAULT NULL,
  `Record` varchar(30) DEFAULT NULL,
  `Notes` varchar(200) NOT NULL,
  PRIMARY KEY (`NumID`),
  KEY `MyitID` (`MyitID`),
  KEY `Date` (`Date`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=42386041 ;

Thanks in advance

Solution

  • Resetting the AUTO_INCREMENT will have little effect on performance. You would slightly reduce the storage size of the field by resetting.

    A better use of your time is running EXPLAIN on your common statements and making sure you have a proper index wherever you're trying to select specific records.