CODEWITHSUNDEEP
mysql
user3489502
user3489502

Reputation: 3611

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             |
+------------------+

Upvotes: 1

Views: 66

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562328

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.

Upvotes: 0

gr1zzly be4r
gr1zzly be4r

Reputation: 2152

You can do this with the following table syntax, but I'm not really sure what performance benefit that you're going to get. I'll look into it and update my answer.

CREATE TABLE your_table (a INT NOT NULL, b INT NOT NULL AUTO_INCREMENT UNIQUE);
ALTER TABLE your_table ADD PRIMARY KEY (a, b);

But, you can't drop the UNIQUE key on b.

Upvotes: 1

Related Questions