Composite Primary Key performance drawback in MySQL

Question

We have a table with a composite Primary key consisting of three fields (and it is in MySQL 5.1). There are near 200 inserts and 200 selects per second on this table, and the size of the table is around 1 million rows and it is increasing.

My question is: does the "Composite Primary Key" decrease the performance of the Inserts and Selects on this table?

Should I be using a simple Auto-Increasing INT ID field instead of a Composite Primary Key? (I think the answer is very much related to the way MySQL handles the Indexes on multiple columns)

Answer 1

INSERT and UPDATE performance varies little: it will be almost same for (INT) and (INT, INT) keys.

SELECT performance of composite PRIMARY KEY depends on many factors.

If your table is InnoDB, then the table is implicitly clustered on the PRIMARY KEY value.

That means that searches for both values will be faster if the both values comprise the key: no extra key lookup will be required.

Assuming your query is something like this:

SELECT  *
FROM    mytable
WHERE   col1 = @value1
        AND col2 = @value2

and the table layout is this:

CREATE TABLE mytable (
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        data VARCHAR(200) NOT NULL,
        PRIMARY KEY pk_mytable (col1, col2)
) ENGINE=InnoDB

, the engine will just need to lookup the exact key value in the table itself.

If you use an autoincrement field as a fake id:

CREATE TABLE mytable (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        col1 INT NOT NULL,
        col2 INT NOT NULL,
        data VARCHAR(200) NOT NULL,
        UNIQUE KEY ix_mytable_col1_col2 (col1, col2)
) ENGINE=InnoDB

, then the engine will need, first, to lookup the values of (col1, col2) in the index ix_mytable_col1_col2, retrieve the row pointer from the index (the value of id) and make another lookup by id in the table itself.

For MyISAM tables, however, this makes no difference, because MyISAM tables are heap organized and the row pointer is just file offset.

In both cases, a same index will be created (for PRIMARY KEY or for UNIQUE KEY) and will be used in same way.