Should I also index the columns included in the PRIMARY KEY?

Question

This question suddenly appeared in my head ... I have a table that links two other tables together based on their identifier. CREATE TABLE as follows:

 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 am wondering if I need to include the last two lines of KEY . Will it improve speed with the following queries, or will individual columns inside the PRIMARY KEY be automatically indexed?

 SELECT * FROM ticket_contact WHERE ticket_id=1; SELECT * FROM ticket_contact WHERE entity_id=1; 

Answer 1

The index created by PRIMARY KEY is the same as any other (potentially complex) UNIQUE index. Therefore, you do not need to create a separate index for ticket_id , since it is included as the main column in the index (ticket_id, entity_id) .

You would like to create a separate index above entity_id if you would normally make queries using this column, regardless of ticket_id .

Answer 2

Think of the PC and index as an easy way to force records in sorted order. Databases can search sorted data much faster than unsorted data (log n vs linear time).

The composite primary key is sorted in the order of the specified columns, so PK (ticket_id, entity_id) sorts ticket_id ASC, entity_id ASC . Since your PC already sorts ticket_ids, it spans the index on ticket_id .

However, sorting by entity_id ASC without any other columns results in a different sort order. If you need to query entity_id , MySQL will perform an index scan (search through each ticket id, then binary_searching the results to match entity_id). Your separate index in entity_id will make queries for entity_id much faster.