user3489502 user3489502 - 3 months ago 8
MySQL Question

Possible to have an ID (auto incremented) as the second column of a MySQL primary key?

Is it possible to have an

id
(auto incremented) as the second column of a MySQL primary key? see
order
table below.

My idea is that it would be faster for MySQL to access
orders
through the primary key index (customer_id, id) when I query the
order
table with
customer_id
.Thanks

Customer
+-------------+
| id (autoinc)| --> primary key
|- - - - - - -|
| name |
| address |
+-------------+
|
|
|
A
Order
+------------------+
| customer_id (fk) | --- primary key
| id (autoinc) | --- primary key
|- - - - - - - - - |
| date |
+------------------+

Answer

Building on the answer from gr1zzly be4r...

InnoDB actually allows you to use an auto-increment as the first column of any KEY, not necessarily just a UNIQUE KEY.

CREATE TABLE `your_table` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`,`b`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

INSERT INTO your_table (a) VALUES (42), (43);
SELECT * FROM your_table;
+----+---+
| a  | b |
+----+---+
| 42 | 1 |
| 43 | 2 |
+----+---+

In other words, the b column doesn't necessarily need to be unique.