3

This question is related to Do MySQL tables need an ID?

There is a meaningless auto_incremental ID acting as PRIMARY KEY for a table, then when I create other KEYs, should I include this ID in the KEYs?

For example, in this table:

CREATE TABLE `location` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(50),
  `states`  varchar(50),
  `city`    varchar(50),
  `county`  varchar(50),
  `zip`     int(5),
  PRIMARY KEY(ID),
  KEY zip1 (zip),
  KEY zip2 (zip, ID)
} ENGINE=InnoDB ;

Because I need to search the table using zip code a lot, so I need a KEY start from zip code. I should use either KEY zip1 or KEY zip2. Which one of these two KEYs is better?

Community
  • 1
  • 1
Ben Lin
  • 807
  • 10
  • 15

1 Answers1

11

For InnoDB, the primary key is always included in secondary indexes;

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

In other words, ID is already included in zip1, and does not have to be mentioned as it is in in zip2.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • Thanks. The official document is very helpful. – Ben Lin Feb 24 '14 at 19:12
  • 8
    This is sort of true. I can add that although the clustered key (`id` in this case) is included in every secondary key *within InnoDB*, MySQL itself doesn't actually know this. There are situations where including the clustered key columns (or a subset of them) will improve the optimizer's decision making. For instance, if you have `SELECT id FROM t WHERE a=5 ORDER BY id DESC` you can avoid an extra sort by making the index on `(a, id)`. Furthermore, including the cluster key in the index definition doesn't take any extra space, since the column data is there already. – jeremycole Feb 25 '14 at 18:26