Is a PRIMARY index the same as a regular INDEX in a mysql table?

Question

I just started using indexes and I have a user_id column with AUTO_INCREMENT, Unique and Primary enabled. Yet I heard that an "index" will make searching way faster, so I clicked the "index" button on phpmyadmin and the "index" button doesn't turn black like the other two: http://puu.sh/2KkSB.png

So then I pressed "Indexes" at the bottom of the screen and this came up:

http://puu.sh/2KkU9.png

So what I wanted to know, is PRIMARY already "indexed"? Will this make searching any faster? Is it the same action as pressing the "index" button on the column? Thanks.

http://stackoverflow.com/questions/1108/how-does-database-indexing-work — Mike Gardner, Apr 30 '13 at 22:06
@MichaelGardner Surely http://stackoverflow.com/questions/1071180/ is more relevant here? — Neil, May 01 '13 at 12:25
@Neil Probably, but he seemed not to understand indexes at all, let alone the MySql implementations. — Mike Gardner, May 01 '13 at 12:28

score 2 · Answer 1 · answered Apr 30 '13 at 22:32

The PRIMARY KEY is the fastest index in a table, and its values must be unique.

Indexes can be UNIQUE too, but they aren't by default.

However, index don't make your tables magically faster. They improve performance some queries, if they are designed to be used by those queries.

score 2 · Answer 2 · answered Apr 30 '13 at 22:33

An index can include multiple columns and a single table can have multiple indexes. The PRIMARY KEY is also an index but is subject to additional constrains other indexes are not:

A table can have only one PRIMARY KEY
The primary key value for each row must be unique (i.e. no duplicated values) and can not be null

To add a column to the PRIMARY KEY you press the 'key' icon next to it. Although this already creates an index (the primary key), it is not the same as pressing the 'index' button as it has additional restrictions.

Is a PRIMARY index the same as a regular INDEX in a mysql table?

2 Answers2