Use of MySQL indexes

1.1 Creation of index
　1.1.1 Creation of primary key index
　Create the primary key index when creating the table
　

create table aaa(id int primary key，name varchar(64) not null default ”);

　Create the table first and then the primary key index
　　

create table aaa(id int，name varchar(64) not null default ”);
　　alter table aaa add primary key(id);

Characteristics of primary key index
1) A table can only have one primary key at most
2) One primary key can point to multiple columns (composite primary key)
3) Primary key index is the most efficient , so we should give the id. Generally, the id is auto-incrementing
　4) The columns of the primary key index cannot be repeated or null
　1.1.2 Create a unique index directly when creating the table, specify a certain column or a certain How many columns are unique indexes
　

mysql> create table aaa(id int，name varchar(64) not null default ”，email varchar(64) not null default ” unique);

　Create a unique index after creating the table
　　

1）mysql> create unique index uni_name on aaa (name);
　　2）mysql> alter table aaa add unique (email);

　Characteristics of unique indexes
　1) A table can There are multiple unique indexes
　2) The unique index cannot be repeated, but if you do not specify not null, the unique index can be null, and there can be multiple.
　3) When to use a unique index? It can only be used when the data in a certain column will not be repeated. When creating a table, specify a certain column or columns as a common index
　

mysql> create table aaa(id int，name varchar(64) not null default ”，namevarchar(64) not null default ” index);

　Create a common index after creating the table

　

　1）mysql> create index ind_name on aaa (name);
　　2）mysql> alter table aaa add index(name);

　Features

1) There can be multiple ordinary indexes in a table, and one ordinary index can also point to multiple columns

2) The data of ordinary index columns can be repeated
3) The efficiency is relatively low
1.1.4 Full-text index Creation
Overview: The full-text index is an index for articles, Chinese characters, and English, which can quickly retrieve a keyword in the article
　

CREATE TABLE articles (
　　id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY，
　　title VARCHAR(200)，
　　body TEXT，
　　FULLTEXT (title，body)
　　) engine myisam charset=utf8;

Use: select * from articles where match(body) against('weather');

Features

1) Mysql's default full-text index, only effective for the myisam storage engine
2) Mysql's default full-text index, only supports English
3) Stop words: For particularly common letters, no index will be built
4) Matching degree: The full-text index is matched according to a certain probability.
How to solve the problem that mysql's full-text index does not support Chinese
1) Use a Chinese search plug-in for mysql mysqlcft
2) You can use the specialized Chinese search engine sphinx Chinese version (coreseek) 1.2 Index query
Desc table name
show keys from table name G
show index from table name G
show indexes from table name G
1.3 Index modification
Delete first and then add
1.4 Index deletion
　DROP INDEX index name ON table;
　ALTERTABLE table name DROP INDEX index name;
　1.6 Precautions for indexes Fields that are frequently used as query conditions should be indexed. Fields with poor uniqueness are not suitable for separate creation. Indexes, even if they are frequently used as query conditions, fields that are frequently updated are not suitable for creating indexes. Fields that do not have WHERE clauses should not be indexed.


The above is the use of MySQL indexes, more related Please pay attention to the PHP Chinese website (www.php.cn) for content!