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)

Gnathion

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.

Duckling

Answer 2

If it's InnoDB, the composite primary key will be included in each entry in each of the secondary indexes.

This means that

• Your secondary indexes will take up as much space as those columns + all the columns in the primary key
• You can use a secondary index as a covering index if all the columns required are contained in the secondary index + pk

These are of course, a disadvantage and an advantage respectively.

Composite primary keys are not necessarily bad, sometimes they can be really helpful because InnoDB clusters them - which means that (disc-bound) range scans over the PK can be satisfied using far fewer IO operations than would be required on a non-clustered index.

Of course if you've got foreign keys in other tables, they're wider as well as they need to include the whole key from your main table.

But I'd say on balance, generally, no. Having a composite primary key does NOT cause a problem by itself. Having a "big" primary key (e.g. big varchars) may do however, if that outweighs the advantages of clustering and being able to use covering indexes.

Yetah

Does that mean that if I have my primary key on (reviewId,userId) and a secondary index on (userId), this index will internally contain (userId,reviewId,userId)? – Geesey

@Benjamin Yes, you're right. It's clearly stated here dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html and in this book shop.oreilly.com/product/0636920022343.do – Electrotechnology

Answer 3

1. Having that composite primary key slows down SELECTs a tiny bit, though the effect is pretty much negligible and not worth worrying about.
2. Having those columns indexed at all slows down your INSERTs, and you certainly are doing enough INSERTs to worry about it. This is much more of a concern if it's a MyISAM table, where an INSERT locks the table, than if it's an InnoDB table. If, by going with the auto_increment primary key, you would be able to leave those columns unindexed, you would benefit from the change. If you would still need to keep those three columns indexed, though (for example, if you need to enforce uniqueness on the combination of them), it isn't going to do anything for you performance-wise.

Dhiren