Mysql index

Advantages of index

Index seems to be a very lofty name. To put it bluntly, it is the latest table of contents of our book.

If you use Xinhua Dictionary to search for the Chinese character "张" without using the table of contents, you may have to go from the first page of Xinhua Dictionary to the last page, which may take two hours. The thicker the dictionary, the more time it will take you. Now you use the directory to search for the Chinese character "Zhang". The first letter of Zhang is z, and the Chinese characters starting with z start from more than 900 pages. With this clue, it may only take a minute for you to search for a Chinese character. This shows the importance of the index. sex.

Indexes are used to quickly find rows that have a specific value in a column.

Without using an index, MySQL must start from the first record and then read the entire table until it finds the relevant row. The larger the table, the more time it takes. If the queried column in the table has an index, MySQL can quickly get to a point where it searches the middle of the data file without having to look at all the data.

Of course, it is not easy to have too many indexes. The more indexes are written, the slower the modification speed. Because when writing modified data, the index must also be modified.

MySQL index type

Index type	Function description
Normal index	The most basic index, it has no restrictions
Unique index	If a row uses a unique index, it is not allowed There are duplicate values ​​in the row data for this column. Each row of data for this column is required to be unique
Primary key index	It is a special unique index that does not allow null values. Generally, the primary key index is created at the same time when creating the table, which is often used for user ID. Similar to the page numbers in the book
Full-text index	For data that requires global search, perform full-text index

# #Note: Please study the following parts after completing 12.7.

Common index

TypeDetailed description Basic syntaxalter table table add index(field)ExampleALTER TABLE Example explanationAdd an index to the username field of the money table##Unique Index

	money ADD INDEX( username);

TypeBasic syntaxExamplemoneyExample explanation

Full text index

Detailed description
alter table table add UNIQUE(field)
ALTER TABLE	ADD UNIQUE(email);
Add a unique index to the email field of the money table

Type	Detailed description
Basic syntax	alter table table add FULLTEXT(field)
Example	ALTER TABLE money ADD FULLTEXT(content);
Example explanation	Add a unique index to the content field of the money table

Primary key index

##Basic syntaxalter table table add PRIMARY KEY(field)ExampleALTER TABLE Example explanationAdd a primary key index to the id field of the money table

Type	Detailed description
	money ADD PRIMARY KEY(id);

You can also declare an index when creating a table

When creating a table, you can declare the index by adding the corresponding type after the create table statement:

PRIMARY KEY (field)

INDEX [index name] (field)
FULLTEXT [index name] (field)
UNIQUE[index name] (field)

Note: The index name in brackets represents optional.

The overall example is as follows:

CREATE TABLE

test (
id INT NOT NULL ,
username VARCHAR(20) NOT NULL ,
password INT NOT NULL ,
content INT NOT NULL , PRIMARY KEY (
id), INDEX pw (
password), UNIQUE (
username), FULLTEXT (
content) ) ENGINE = InnoDB;