4
  1. Does primary key constraint on a mysql database table column automatically mean there is an index created on given column?
    1. If it does, could we generalize it for any database or it just depends on database implementation?
    2. If it does and also if the primary key is composite, is there an index created per each such column?
  2. Does it make any sense to create (additional?) index via the key constraint on the column already being the primary key?
sharpener
  • 1,383
  • 11
  • 22

2 Answers2

4

  1. In MySQL a PRIMARY or UNIQUE KEY creates an index on the columns defined in the constraint. If there are multiple columns a composite index is created.

    If its an InnoDB table the PRIMARY KEY also becomes the clustered index for the table.

  2. It doesn't make sense to add additional indexes with the same definitions as a PRIMARY/UNIQUE.

For other RDBMS an index will be required for these constraints. Even if you are allowed to create a constraint without an appropriate index, it will be required to get any reasonable performance.

Community
  • 1
  • 1
Vatev
  • 7,493
  • 1
  • 32
  • 39
1
  1. Yes, primary key is an index in MySQL too.

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.

https://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html

For the differences between primary key and unique index, you can see: difference between primary key and unique key

  1. No, you will have two indices and it will take more of disk space.
Community
  • 1
  • 1
MartyIX
  • 27,828
  • 29
  • 136
  • 207