Four types of MySQL database indexes and related index creation

The following articles mainly introduce the index types of MySQL databases, including common indexes, unique indexes, primary key indexes and primary key indexes, as well as the actual application or creation of these indexes, the following describes the main content of the article.

1) Common Index

This is the most basic MySQL database index, which has no restrictions. It can be created in the following ways:

Create an index

 
 

  
  
CREATE INDEX indexName ON mytable(username(length)); 
 
 

For CHAR and VARCHAR types, the length can be smaller than the actual length of the field; For BLOB and TEXT types, the length must be specified, the same below.

Modify Table Structure

ALTER mytable add index [indexName] ON (username (length)

 
 

  
  
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );  
 
 

Syntax for deleting an index:

 
 

  
  
DROP INDEX [indexName] ON mytable;  
 
 

2) unique index

It is similar to the previous normal index. The difference is that the values in the index column of the MySQL database must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:

Create an index

Create unique index indexName ON mytable (username (length) Modify Table Structure

ALTER mytable add unique [indexName] ON (username (length)

 
 

  
  
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );  
 
 

3) Primary Key Index

It is a special unique index and does not allow null values. Generally, when creating a table, you can create a primary key index at the same time:

 
 

  
  
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );  
 
 

Of course, you can also use the ALTER command. Remember: A table can only have one primary key.

4) Composite Index

To visually compare Single-Column indexes and composite indexes, add multiple fields to the table:

 
 

  
  
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );  
 
 

To further extract MySQL efficiency, you must consider establishing a composite index. Create name, city, and age in an index:

Alter table mytable add index name_city_age (name (10), city, age); when creating a TABLE, usernname is 16 in length, and 10 is used here. This is because the name length generally does not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.

If a single column index is created on usernname, city, and age respectively, the table has three single column indexes, and the query efficiency will be significantly different from that of the preceding composite indexes, far lower than our combined index. Although there are three indexes at this time, MySQL can only use one of them, which seems to be the most efficient single-column index.

The establishment of such a composite index is actually equivalent to the establishment of the following three groups of composite MySQL database indexes:

Why does usernname, city, age usernname, and city usernname have no combined indexes such as city and age? This is because MySQL Composite Index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. This composite index is not used for queries that contain these three columns. The following SQL statements use this composite MySQL database index:

SELECT * FROM mytable WHREE username = "admin" AND city = "Zhengzhou" SELECT * FROM mytable WHREE username = "admin" AND the following are not used:

SELECT * FROM mytable WHREE age = 20 AND city = "Zhengzhou" SELECT * FROM mytable WHREE city = "Zhengzhou"