Mysql – Should I also index columns included in a PRIMARY KEY

database-designindexingMySQLprimary-key

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;

Best Answer

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.

Related Topic