Shulou(Shulou.com)06/01 Report--

Let me tell you a little bit about how to use mysql index. have you known about similar topics before? If you are interested, let's take a look at this article. I believe it will be more or less helpful to you after reading the operation method of using mysql index.

The purpose of the 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, then find the y letter from the bottom down, and then find the rest of the sql. If there is no index, then you may need to look through all the words to find what you want.

When creating an index, you need to consider which columns will be used in the SQL query, and then create one or more indexes for those columns. In fact, an index is also a table that holds a 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 just used to speed up queries. Database search engines use indexes to quickly locate records.

Mysql has four indexes (primary key index / general index / full-text index / unique index)

1. Addition of index

1.1 addition of 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'); / / here id is the primary key of the table

If you do not specify a primary key index when you create a table, you can also add:

Alter table table_name add primary key (column name)

1.2 General Index

General indexes are usually added after the table is created.

Create index index name on table_name (column1,column2); alter table table_name add index index name (column1,column2)

1.3 full-text index

First of all, full-text indexing is mainly aimed at text files, such as articles, titles, and full-text indexing is valid only by MyISAM (InnoDB also supports full-text indexing 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 errors in using full-text indexing:

Select * from c where content like "% mysql%"

Full-text indexing is not used here, but can be viewed with explain. Correct usage:

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

Remarks:

1. In mysql, fulltext index is only valid for myisam.

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

3. The method to use is match (field name..) Against ('keyword')

1.4 unique index

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

The name in the d table is the unique index. The unique index can have multiple null and cannot be duplicated content.

Compared to the primary key index, the primary key field cannot be null or duplicated

two。 Query index

Show indexes from table_name;show keys from table_name

3. Delete index

Alter table table_name drop index index name

What do you think of the operation method of using mysql index? what do you think of this article and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.