I know how to use INDEX as in the following code. And I know how to use foreign key and primary key.
CREATE TABLE tasks (
task_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
parent_id INT UNSIGNED NOT NULL DEFAULT 0,
task VARCHAR(100) NOT NULL,
date_added TIMESTAMP NOT NULL,
date_completed TIMESTAMP,
PRIMARY KEY (task_id),
INDEX parent (parent_id),
....
However I found a code using KEY instead of INDEX as following.
...
KEY order_date (order_date)
...
I could not find any explanation on the official MySQL page. Could anyone tell me what is the differences between KEY and INDEX?
The only difference I see is that when I use KEY ..., I need to repeat the word, e.g.
KEY order_date (order_date).
There's no difference. They are synonyms.
From the CREATE TABLE manual entry:
KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when 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."
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
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.
A key is a set of columns or expressions on which we build an index.
While an index is a structure that is stored in database, keys are strictly a logical concept. Index help us in fast accessing a record, whereas keys just identify the records uniquely. Every table will necessarily have a key, but having an index is not mandatory.
Check on https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- Finding the index of an item in a list
- Is there a REAL performance difference between INT and VARCHAR primary keys?
- Should I use the datetime or timestamp data type in MySQL?
- Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
- What's the difference between utf8_general_ci and utf8_unicode_ci?
- MySQL: @variable vs. variable. What's the difference?
- How can I get a list of user accounts using the command line in MySQL?
- Difference between Key, Primary Key, Unique Key and Index in MySQL
- How do I import an SQL file using the command line in MySQL?
KEY keyname (column_name)when I created the table withINDEX keyname (column_name). The fact that it is a synonym explains it perfectly.PRIMARY KEYcan also be specified as justKEYwhen given in a column definition." What does this mean?