Should I also index columns included in a PRIMARY KEY?

Question

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;

Answer 1

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.

Answer 2

Think of a PK and an Index as simply a way of forcing your records into sorted order. Databases can search through sorted data much much faster than unsorted data (log n vs linear time).

A composite primary key sorts in the order of columns given, so PK (ticket_id, entity_id) sorts ticket_id ASC, entity_id ASC. Since your PK already sorts ticket_ids, it covers the index on ticket_id.

However, sorting by entity_id ASC with no other columns results in a different sort order. If you needed to query on entity_id often, MySQL will perform an index scan (searching through each ticket_id, then binary_searching the results for the matching entity_id). Your separate index on entity_id will make queries for entity_id much faster.