2

I ran a comparison INSERTing rows into an empty table using MySQL 5.6.

Each table contained a column (ascending) that was incremented serially by AUTO_INCREMENT, and a pair of columns (random_1, random_2) that receive random, unique numbers.

In the first test, ascending was PRIMARY KEY and (random_1, random_2) were KEY. In the second test, (random_1, random_2) were PRIMARY KEY and ascending was KEY. 

CREATE TABLE clh_test_pk_auto_increment (
   ascending_pk       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- PK
   random_ak_1        BIGINT UNSIGNED NOT NULL,                -- AK1
   random_ak_2        BIGINT UNSIGNED,                         -- AK2
   payload            VARCHAR(40),
   PRIMARY KEY        ( ascending_pk ),
   KEY                ( random_ak_1, random_ak_2 )
)  ENGINE=MYISAM 
   AUTO_INCREMENT=1 
   ;

CREATE TABLE clh_test_auto_increment (
   ascending_ak       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- AK
   random_pk_1        BIGINT UNSIGNED NOT NULL,                -- PK1
   random_pk_2        BIGINT UNSIGNED,                         -- PK2
   payload            VARCHAR(40),
   PRIMARY KEY        ( random_pk_1, random_pk_2 ),
   KEY                ( ascending_ak )
)  ENGINE=MYISAM 
   AUTO_INCREMENT=1 
   ;

Consistently, the second test (where the auto-increment column is not the PRIMARY KEY) runs slightly faster -- 5-6%. Can anyone speculate as to why?

Chap
  • 3,649
  • 2
  • 46
  • 84
  • I could speculate, but there is very little point to do so until you tell us what the "test" is. – Hazzit Mar 11 '13 at 21:06
  • Insert as fast as possible 1 million rows. – Chap Mar 11 '13 at 21:25
  • I do not think the second test does what you think it does. See this: http://dba.stackexchange.com/questions/3467/mysql-why-is-auto-increment-limited-to-just-primary-keys – Hazzit Mar 11 '13 at 22:10
  • Why do you think that? Some of the commentary states that AUTO_INCREMENT *does* work on non-PK (and my test bears that out). As to "why?", we have a couple of massive joins that need to be done, one with another table in **ascending** order, and one with a table in **random** order (not really random, but random with respect to the ascending order). – Chap Mar 12 '13 at 01:08

1 Answers1

2

Primary keys are often used as the sequence in which the data is actually stored. If the primary key is incremented, the data is simply appended. If the primary key is random, that would mean that existing data must be moved about to get the new row into the proper sequence. A basic (non-primary-key) index is typically much lighter in content and can be moved around faster with less overhead.

I know this to be true for other DBMS's; I would venture to guess that MySQL works similarly in this respect.

UPDATE

As stated by @BillKarwin in comments below, this theory would not hold true for MyISAM tables. As a followup-theory, I'd refer to @KevinPostlewaite's answer below (which he's since deleted), that the issue is the lack of AUTO_INCREMENT on a PRIMARY KEY - which must be unique. With AUTO_INCREMENT it's easier to determine that the values are unique since they are guaranteed to be incremental. With random values, it may take some time to actually walk the index to make this determination.

PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • Not in the MyISAM storage engine; there's no difference between primary key and secondary key. – Bill Karwin Mar 11 '13 at 21:35
  • Primary is implicitly unique, the secondary - at least in this test - is not declared as such. – Chap Mar 11 '13 at 21:44
  • @BillKarwin - does the MyISAM engine not cluster on the primary key? A similar answer was suggested [here](http://stackoverflow.com/questions/5465233/mysql-is-out-of-order-inserts-into-pk-b-tree-slower-than-out-of-order-inserts) that assumes clustering is being done on the primary key... – PinnyM Sep 17 '13 at 20:25
  • Right; MyISAM does not have any support for a clustered index. Both primary and secondary indexes are stored together in .MYI files. Data is stored in .MYD files, in storage order. If you always INSERT, then the .MYD file contains rows in insert order. But as you UPDATE and DELETE data, space will be reassigned and rows will become stored out of order. See my answer to [How Data is ordered in MySql by Default](http://stackoverflow.com/questions/15346829/how-data-is-ordered-in-mysql-by-default/15346918#15346918) – Bill Karwin Sep 17 '13 at 21:51
  • But your description is correct for InnoDB. Every InnoDB table is stored as a clustered index on the primary key. So there's a (small) advantage to use an ascending auto-increment primary key in InnoDB -- new rows are appended to the right-hand end of the clustered index. – Bill Karwin Sep 17 '13 at 21:55