Composite Primary Key performance drawback in MySQL

by

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.

More Related Contents:

  1. How to properly create composite primary keys – MYSQL
  2. Is there a REAL performance difference between INT and VARCHAR primary keys?
  3. MyISAM versus InnoDB [closed]
  4. How do I specify unique constraint for multiple columns in MySQL?
  5. MYSQL OR vs IN performance
  6. How to Perform an UPSERT so that I can use both new and old values in update part
  7. JDBC batch insert performance
  8. SQL Performance UNION vs OR
  9. Difference between Key, Primary Key, Unique Key and Index in MySQL
  10. Why is MySQL InnoDB insert so slow?
  11. MySQL vs MongoDB 1000 reads
  12. auto increment primary leaving gaps in counting
  13. Remove Primary Key in MySQL
  14. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  15. What is better in MYSQL count(*) or count(1)?
  16. ALTER TABLE to add a composite primary key
  17. MySql views performance [closed]
  18. “SELECT COUNT(*)” is slow, even with where clause
  19. MySQL: Inner join vs Where [duplicate]
  20. MySQL load data infile – acceleration?
  21. Strings as Primary Keys in MYSQL Database [closed]
  22. MySQL: Many tables or many databases?
  23. How to make a primary key start from 1000?
  24. sql group by versus distinct
  25. Defining Composite Key with Auto Increment in MySQL
  26. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  27. Why is connecting to MySQL server so slow?
  28. Mysql performance on 6 million row table
  29. Disadvantages of quoting integers in a Mysql query?
  30. Optimizing MySQL for ALTER TABLE of InnoDB

Leave a Comment