Is the primary key automatically indexed in MySQL?
MysqlDatabaseIndexingKeyMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
According to http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html it would appear that this is would be implicit
Solution 3 - Mysql
Even though this was asked in 2009 figured I'd post an actual reference to the MySQL documentation on primary keys. http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html
> 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.
Solution 4 - Mysql
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.
Solution 5 - Mysql
You do not have to explicitly create an index for a primary key... it is done by default.
Solution 6 - Mysql
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 ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
Solution 7 - Mysql
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.
Solution 8 - Mysql
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 foo
!
Solution 9 - Mysql
Yes, 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.