Xin Xing makes mysql run faster. Section 2 index _ MySQL

If we regard our database information as a book or dictionary, the index can be understood as its directory. if we create an excellent directory, we will retrieve the information much faster, if we create a dregs index, the entire system may also be broken down. If we regard our database information as a book or dictionary, the index can be understood as its directory. if we create an excellent directory, we will retrieve the information much faster, if we create a dregs index, the entire system may also be broken down.

There are four types of indexes: primary key index, full-text index, unique index, and general index. The so-called primary key index means that when we create a table, if we specify a primary key, it will automatically become a primary key index, for example, the SQL statement is as follows (here the id automatically becomes the primary key index ):

create table xin(id int unsigned primary key auto_increment,name   varchar(32) not null default '');

In general, for common indexes, we can specify indexes when creating a table or when creating a table. next we will create a table in the county and then add indexes as an example, check the code:

# Create table xin (id int unsigned, name varchar (32) not null default ''); # create index xiaohei on xin (id) on the table );

Here we will talk about the format of adding an index here: create index name on table name (column name );

Next, let's talk about full-text indexing. The so-called full-text indexing mainly searches for string information from the database. for example, when we visit many forums, its search function requires full-text indexing, our full-text index mainly targets file and text indexes. At present, the full-text index is still only valid for the engine MyIASM. let's just specify the engine of the table. See the following code:

# Create an article table and set the index create table article (id int primary key, title varchar (200) not null, body text, fulltext (title, body) engine = myisam;

We should use match and against when using this full-text index. See the following operations:

# In this way, we can quickly use full-text indexes to find the content select * from article where match (title, body) against ('sin ');

However, the full-text index provided by mysql does not support Chinese characters. we can consider using sphinx to support Chinese characters.

For unique Indexes, that is, unique Indexes, we can add a unique after the column-level integrity constraint. Note that the fields modified by unique can be empty, in addition, there can be multiple blank ones. of course, you can also create them like creating a common index, but at this time, the index must be prefixed with a unique modifier.

For the deletion of indexes, you can use the alter table name drop index name, but I think it is better to use the drop index name on table name.

So how can we view the index of a table? we can view the index by using the desc table name, you can also use show index from table name or show index from table name to view the index of a table.