The difference between key and index in MySQL

Let's look at the code first:

ALTER TABLE reportblockdetail ADD KEY taskcode (taskcode)
ALTER TABLE reportblockdetail DROP KEY taskcode

Well, this is indeed where it gets confusing.

We may not pay attention to this problem when using MySQL, because in most cases they show similar effects, but they still cannot be equated (at least in theory)

The difference between an index and a key lies in their different starting points. The index is responsible for maintaining the search and operation speed of the table, while the key is responsible for maintaining the integrity of the table.

If you have this confusion, it is probably due to a strange phenomenon in MySQL:

• Indexes in MySQL are constraint indexes (that is, creating an index automatically creates a constraint)
• And creating constraints in MySQL will automatically come with indexes.

It's strange, right? They are two different things, but when they are created, they are attached to each other. Why do they do this? Because the reasons are:

The constraint effect in MySQL is achieved through indexes. The MySQL database determines whether the current column is unique through unique indexes.

Finally, let’s summarize:

• Constraints include primary key constraints, unique constraints, foreign key constraints, not null constraints, check constraints (check constraints cannot be used at all in MySQL, but can be established normally), etc.
• Indexes include common indexes, primary key indexes, unique indexes, joint indexes, full-text indexes, etc.
• Both can be achieved when creating a table. After the table is created, the alter statement can be used to create and delete it. For specific statements, please search Baidu for the above two points. I have tested them in MySQL5.5 and innoDB storage engine.

In theory, MySQL key and index cannot be equated, they are not the same thing, but in actual use, there is basically no difference between them.

Content extension:

Difference between key and primary key

CREATE TABLE wh_logrecord ( 
logrecord_id int(11) NOT NULL auto_increment, 
user_name varchar(100) default NULL, 
operation_time datetime default NULL, 
logrecord_operation varchar(100) default NULL, 
PRIMARY KEY (logrecord_id), 
KEY wh_logrecord_user_name (user_name) 
)

Difference between KEY and INDEX

Note: I am still confused about this part.
KEY is often a synonym for INDEX. PRIMARY KEY can also be specified as just KEY if the keyword attribute PRIMARY KEY is given in the column definition. This is done for compatibility with other database systems. PRIMARY KEY is a unique KEY, in which case all keyword columns must be defined as NOT NULL. If the columns are not explicitly defined as NOT NULL , MySQL should define them implicitly. A table has only one PRIMARY KEY.

The difference between Index and Key in MySQL

Key is the key value, which is part of the relational model theory. For example, there are primary keys and foreign keys, which are used for data integrity checks and uniqueness constraints. Index is at the implementation level. For example, you can create an index for any column in a table. Then, when the indexed column is in the Where condition in the SQL statement, you can quickly locate the data and retrieve it quickly. As for Unique Index, it is just a type of Index. The establishment of a Unique Index indicates that the data in this column cannot be repeated. I guess MySQL can make further special optimizations for Unique Index type indexes.

Therefore, when designing a table, the Key only needs to be at the model level, and when query optimization is required, indexes can be created for the relevant columns.

In addition, in MySQL, for a Primary Key column, MySQL has automatically created a Unique Index for it, so there is no need to create an index on it again.

The above is the detailed content of the difference between key and index in MySQL. For more information about the difference between key and index in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!