How to use mysql index

How to use mysql index: [alter table table_name add index index name (column)], which means adding a normal index. The purpose of mysql index is to improve query efficiency.

The purpose of mysql index is to improve query efficiency, which can be compared to a dictionary. If we want to look up the word "mysql", we definitely need to locate the m letter, and then start from Go down to find the y letter, and then find the remaining sql. Without an index, you may need to look through all the words to find what you want.

(Recommended tutorial: mysql video tutorial)

When creating an index, you need to consider which columns will be used for SQL queries, and then create one or more columns for these columns index. In fact, an index is also a table that holds the primary key or index field, and a pointer that points each record to the actual table. Indexes are not visible to database users; they are only used to speed up queries. Database search engines use indexes to quickly locate records.

Mysql has four kinds of indexes (primary key index/normal index/full-text index/unique index)

1. Adding index

1.1 Adding primary key index

When a table sets a column as the primary key, the column is the primary key index

create table a(  
id int primary key auto_increment,  
name varchar(20) not null default ''  
);  
//这里id就是表的主键

If the primary key index is not specified when creating the table, you can also add it after creating the table:

alter table table_name add primary key (column name);

1.2 Ordinary index

Ordinary index is usually added after the table is created.

create index 索引名 on table_name(column1,column2);
alter table table_name add index 索引名(column1,column2);

1.3 Full-text index

First of all, full-text index is mainly for text files , such as articles, titles, and full-text indexes are only valid for MyISAM (InnoDB also supports full-text indexes after mysql5.6)

create table c(  
id int primary key auto_increment ,  
title varchar(20),  
content text,  
fulltext(title,content)  
)engine=myisam charset utf8;  
  
insert into c(title,content) values  
    ('MySQL Tutorial','DBMS stands for DataBase ...'),  
    ('How To Use MySQL Well','After you went through a ...'),  
    ('Optimizing MySQL','In this tutorial we will show ...'),  
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),  
    ('MySQL vs. YourSQL','In the following database comparison ...'),  
    ('MySQL Security','When configured properly, MySQL ...');

Common mistakes when using full-text indexes:

select * from c where content like "%mysql%";

Full-text will not be used here Index can be viewed using explain. Correct usage:

select *  from c where match(title,content) against ('MYSQL');

Remarks:

1. The fulltext index in mysql is only effective for myisam

2. The fulltext provided by mysql itself is effective for English->sphinx( coreseek) technology to process Chinese

3. The usage method is match(field name..) against('keyword')

1.4 unique index

create table d(id int primary key auto_increment , name varchar(32) unique)

d name in the table It is the unique index. The unique index can have multiple nulls and cannot be repeated content

Compared with the primary key index, the primary key field cannot be null or repeated

2. Query index

show indexes from table_name;
show keys from table_name;

3. Delete index

alter table table_name drop index 索引名;

The above is the detailed content of How to use mysql index. For more information, please follow other related articles on the PHP Chinese website!