Sinsing let MySQL run faster part of the index on section II

If we take our database information as a book or a dictionary, then the index can be understood as its directory, if we create a good directory, then we will retrieve information much faster, if we create a slag residue index, it may also bring down the entire system.

Index we are divided into four categories, usually divided into four types, namely, primary key index, full-text index, unique index, ordinary index, which is divided by the type of index. The so-called primary key index, that is, when we create a table, if we specify a primary key, then it will automatically become the primary key index, such as the SQL statement as follows (here the ID will automatically become the primary key index):

CREATE table xin (id int unsigned primary key auto_increment,name   varchar (+) NOT null default ');

In general, for normal indexes, we can specify the index when we create the table, or we can specify the index when we create the table, let's take a look at the code in the county to create the table and then add the index as an example:

#创建一个数据表create table xin (id int unsigned,name   varchar (+) NOT null default '); #在该表上创建一个索引create index Xiaohei  On Xin (ID);

Here's to say, here's the format for adding an index is: Create index name on table name (column name);

Then say the full-text index, the so-called full-text index, mainly from the database search string information, such as we visit a lot of forums, its search function is particularly required full-text index, our full-text index mainly for the index of files, text, and at present, the full-text index is still only valid for the engine myiasm, Let's specify the engine of the table and see the following code:

#创建一个文章表, and set the index CREATE table article (ID int primary key,title varchar) NOT NULL, body  text,fulltext (title,body)) engine = MyISAM;

Then we should use match and against when we use this full-text index, and see the following actions:

#按照这种方式我们可以快速的使用全文索引来查找内容select * from article where match (Title,body)  against (' Xin ');

But this full-text index from MySQL does not support Chinese, and we can consider using Sphinx to support Chinese, which is not introduced here.

For a unique index, which is a unique index, we will add a unique to the column-level integrity constraint, and it is important to note that a uniquely decorated field can be empty, and that there can be more than one, and of course it can be created as a normal index. At this point, however, the index must precede a unique modifier.

For the deletion of the index, you can use the ALTER TABLE table name DROP index name, but I feel we are better off using the drop index name on table name.

So how do we look at the index of a table, we can use the "DESC table name" method to view the index, we can also using "Show index from table name" or "Show index from table name" To view the index of a table.