MySQL index type Summary

This article mainly introduces seven different MySQL index types. Indexing a field in a MySQL database table can greatly improve the actual query speed. Through clever use of these indexes, we can make MySQL query and operation more efficient.

Index is the key to quick search. The establishment of MySQL indexes is very important for the efficient operation of MySQL.

The following describes several common MySQL index types:

Index fields in database tables can greatly improve the query speed. Suppose we have created a mytable table:

 
 

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

We randomly inserted 10000 records, one of which is 5555 and admin.

Search for the username = "admin" Record

 
 

  
  
SELECT * FROM mytable WHERE username='admin'; 
 
 

If an index has been created on the username, MySQL can find this record accurately without scanning. On the contrary, MySQL scans all records to query 10000 records.

Indexes are divided into single-column indexes and composite indexes. A single-column index contains only one column. A table can have multiple single-column indexes, but this is not a combination index. A composite index contains multiple columns.

MySQL indexes include:

(1) Common Index

This is the most basic index with 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)) 
 
 

Specify

 
 

  
  
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) MySQL index type: unique index

It is similar to the previous normal index. The difference is that the value of the index column 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)) 
 
 

Specify

 
 

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

(3) MySQL index type: 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); 
 
 

The usernname length is 16 when the table is created, 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 composite indexes:

 
 

  
  
usernname,city,age usernname,city usernname 
 
 

The above content is an introduction to some of the content of the MySQL index type. I hope you will have some gains.