4 types of MySQL database indexes and related index creation

The following article mainly describes the MySQL database index type, including ordinary index, unique index, primary key index and primary key index, as well as the actual application of these indexes or create a detailed introduction, the following is the main content of the article description.

(1) General index

This is the most basic MySQL database index and it has no limitations. It is created in the following ways:

Create an index

1. CREATE INDEX indexname on mytable (username (length));

If it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and text type, length must be specified.

Modify Table Structure

ALTER mytable ADD INDEX [IndexName] on (username (length)) When creating a table, specify directly

1. CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, INDEX [IndexName] (username (length)));

Syntax for dropping an index:

1. DROP INDEX [IndexName] on mytable;

(2) Unique index

It is similar to the previous normal index, except that the values of the MySQL database index columns must be unique, but allow null values. If it is a composite index, the combination of column values must be unique. It is 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)) When creating a table, specify directly

1. CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length)));

(3) Primary key index

It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:

1. CREATE TABLE mytable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY KEY (id));

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

(4) Combined index

To visually compare single-column and composite indexes, add multiple fields to the table:

1. CREATE TABLE mytable (ID int NOT NULL, username varchar (+) NOT NULL, City VARCHAR () is not null, and age INT is not null);

To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:

ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age); When the table is built, the usernname length is 16, which is used here in 10. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.

If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.

The establishment of such a composite index, in fact, is equivalent to establishing the following three sets of MySQL database index:

Usernname,city,age usernname,city Usernname Why not city,age such a combination index? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined MySQL database index:

SELECT * FROM MyTable whree username= "admin" and city= "Zhengzhou" select * FROM MyTable whree username= "admin" and the next few will not be used:

SELECT * FROM MyTable whree age=20 and city= "Zhengzhou" select * FROM MyTable whree city= "Zhengzhou"

4 types of MySQL database indexes and related index creation