MySQL Performance – Does INT Primary Key Insert Order Affect Query Performance?

MySQLperformancequery-performance

Table t has an Int type primary key id without "Auto Increment"

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Use the following three ways to insert data:

Insert sequence id: 1-10000
Descending insert id: 10000-1
Random insert id: 1-10000

Does the order in which rows are inserted as suggested above affect performance in select, order by id and query operations?

Best Answer

I already answered a question like this

Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
Jun 28, 2012 : Benefits of BTREE in MySQL

Of the three methods to insert data you mentioned, I would easily choose method #3 : Random insert id: 1-10000. Why ?

Ascending or Descending Ordered Insertion into an index structure will generate root and node splits 45% of the time as I mentioned in my two earlier posts.

In contrast, the ordering does not matter when it comes to reloading of a table from a mysqldump or upon the execution of a LOAD DATA INFILE command. That's because of the use of sorting mechanisms after the completion of the data load of the table and the index creation can be performed.

For example, look at how a MyISAM table is reloaded.

Data Pages for .MYD are populated
Index Pages for .MYI are populated with unique keys in parallel with Data Page Reload
Secondary Indexes are built by the internal execution of Repair by sorting

In that instance, order does not matter.

Switching gears to SELECT ORDER BY, performance could be nominally affected if needed keys from indexes are so split apart that multiple index pages must be accessed for ranges of data, this again leads me to choose method #3. The lower the frequency of index page splits, the fewer pages need to be read by an ORDER BY request.

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – Does the order of keys in compound Primary key make any difference

The order of keys can make a difference depending on your access pattern.

Example...

You want to find MAX(idPackageVariant) for a singleidHolidayPackage

With idHolidayPackage as the second column, then the index will be scanned. With idHolidayPackage as the first, it can be seeked which is more efficient.

This is a specifiy MySQL problem of course. IIRCl you'll need to do this. InnoDB uses the first unique key as the clustered index which may or may not be the primary key. However, this may not be the case

CREATE  TABLE IF NOT EXISTS `wah_schema`.`HolidayPackageVariant` (
  `idHolidayPackage` INT UNSIGNED NOT NULL ,
  `idPackageVariant` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `packageType` VARCHAR(45) NOT NULL ,

  UNIQUE INDEX `idHolidayPackage_UNIQUE` (`idHolidayPackage` ASC, `idPackageVariant` ASC, `packageType` ASC) ,

  PRIMARY KEY (`idHolidayPackage`, `idPackageVariant`) ,

  -- not needed INDEX `fk_HolidayPackageVariant_HolidayPackage` (`idHolidayPackage` ASC) ,

  CONSTRAINT `fk_HolidayPackageVariant_HolidayPackage`
    FOREIGN KEY (`idHolidayPackage` )
    REFERENCES `wah_schema`.`HolidayPackage` (`idHolidayPackage` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Alternatively, you can just have idPackageVariant as the primary and clustered key for efficiency.

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Does the order of keys in compound Primary key make any difference

Related Question