Menu
A key uniquely identifies a row in a table. An index is the order of rows based a field in a table. A table can have multiple indexes, because an index can just be a certain order of a set fields the system uses to search on and then looks up the actual row.
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs.
If there are many rows covered by the index than it's can be faster to simply do a table scan instead. An index adds some overhead while querying so if the between covers more than 80% (completely arbitrary, but you get the idea) of the rows in the table, the table scan can be faster.
But in the database world, it's actually not necessary to create an index on the primary key column — the primary index can be created on any non primary key column as well.
There's no difference. They are synonyms.
From the CREATE TABLE manual entry:
KEYis normally a synonym forINDEX. The key attributePRIMARY KEYcan also be specified as justKEYwhen given in a column definition. This was implemented for compatibility with other database systems.
Here is a nice description about the "difference":
"MySQL requires every Key also be indexed, that's an implementation detail specific to MySQL to improve performance."
It is mentioned as a synonym for INDEX in the 'create table' docs:
MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.17 CREATE TABLE Syntax
@Nos already cited the section and linked the help for 5.1.
Like PRIMARY KEY creates a primary key and an index for you,
KEY creates an index only.
Keys are special fields that play very specific roles within a table, and the type of key determines its purpose within the table.
An index is a structure that RDBMS(database management system) provides to improve data processing. An index has nothing to do with a logical database structure.
SO...
Keys are logical structures you use to identify records within a table and indexes are physical structures you use to optimize data processing.
Source: Database Design for Mere Mortals
Author: Michael Hernandez
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With