The Question :
248 people think this question is useful
Do you need to explicitly create an index, or is it implicit when defining the primary key? Is the answer the same for MyISAM and InnoDB?
The Question Comments :
The Answer 1
299 people think this answer is useful
The primary key is always indexed. This is the same for MyISAM and InnoDB, and is generally true for all storage engines that at all supports indices.
The Answer 2
31 people think this answer is useful
The Answer 3
16 people think this answer is useful
Even though this was asked in 2009 figured I’d post an actual reference to the MySQL documentation on primary keys.
The primary key for a table represents the column or set of columns
that you use in your most vital queries. It has an associated index,
for fast query performance
For MySQL 5.0 reference see: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are
stored in B-trees. Exceptions are that indexes on spatial data types
use R-trees, and that MEMORY tables also support hash indexes.
The Answer 4
11 people think this answer is useful
The primary key is implicitly indexed for both MyISAM and InnoDB. You can verify this by using EXPLAIN on a query that makes use of the primary key.
The Answer 5
9 people think this answer is useful
You do not have to explicitly create an index for a primary key… it is done by default.
The Answer 6
8 people think this answer is useful
I guess this is the answer
mysql> create table test(id int primary key, s varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> show indexes from test \G
*************************** 1. row ***************************
1 row in set (0.00 sec)
The Answer 7
7 people think this answer is useful
Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as “order by”.
You might be working on a more complex database, so it’s good to remember a few simple rules.
- Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated.
Indexes speed up where clauses and order by.
Remember to think about HOW your data is going to be used when building your tables.
There are a few other things to remember. If your table is very small, i.e., only a few employees, it’s worse to use an index than to leave it out and just let it do a table scan.
Indexes really only come in handy with tables that have a lot of rows.
Another thing to remember, that is a con in the situation of our employee’s database, is that if the column is a variable length, indexes (as well as most of MySQL) perform much less efficiently.
Don’t forget joins too! Indexed join fields speed things up.
The Answer 8
4 people think this answer is useful
The primary key is always automatically indexed and unique. So, beware not to create redundant indexes.
For instance, if you created a table as such
CREATE TABLE mytable (foo INT NOT NULL PRIMARY KEY, bar INT NOT NULL, baz INT NOT NULL,
UNIQUE(foo), INDEX(foo)) ENGINE=InnoDB;
because you want to index the primary key and enforce an uniqueness constraint on it, you’d actually end up creating three indexes on
The Answer 9
0 people think this answer is useful
One can think of a primary key column as any other indexed column with the constraints of primary key brings with it.
In most of the use cases we need both primary key, and indexed column/columns in a table, because our queries to table may filter rows based on column/columns which is not primary key, in that case we usually index those column/columns as well.