Should I also index columns included in a PRIMARY KEY?
database-design, indexing, mysql, primary-key
Solution
An index created by `PRIMARY KEY` is the same as any other (potentially composite) `UNIQUE` index. So you needn't create a separate index for `ticket_id` as it is included as the major column in the `(ticket_id, entity_id)` index.
You would want to create a separate index over `entity_id` if you were commonly doing queries using that column independently of the `ticket_id`.
Problem
This question suddenly popped into my head... I have a table that ties two other tables together based on their ID. The `CREATE TABLE` looks like this: ``` CREATE TABLE `ticket_contact` ( `ticket_id` INT NOT NULL, `entity_id` INT NOT NULL, `notify` INT NOT NULL DEFAULT 0, PRIMARY KEY (`ticket_id`, `entity_id`), KEY `ticket_id` (`ticket_id`), KEY `entity_id` (`entity_id`) ) ``` I'm wondering if there is any need to include those last two `KEY` lines. Will it give me improved speed with the following queries, or will individual columns within a `PRIMARY KEY` automatically be indexed? ``` SELECT * FROM ticket_contact WHERE ticket_id=1; SELECT * FROM ticket_contact WHERE entity_id=1; ```