Database-mysql Index

MySQL Index

MySQL indexing is important for the efficient operation of MySQL, and indexing can greatly improve the retrieval speed of MySQL.

For example, if the reasonable design and use of the indexed MySQL is a Lamborghini, then no design and use of the index of MySQL is a human tricycle.

Index sub-column indexes and composite indexes. A single-column index, that is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index. A composite index, that is, an index that contains multiple columns.

When you create an index, you need to make sure that the index is the condition that is applied to the SQL query statement (generally as the condition of the WHERE clause).

In fact, the index is also a table that holds the primary key and index fields and points to the records of the entity table.

The benefits of using indexes are described above, but excessive use of indexes will result in abuse. Therefore, the index also has its drawbacks: Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file.

Index files that create indexes that consume disk space.

Normal index Creation Index

This is the most basic index and it has no limitations. It is created in the following ways:

CREATE INDEX indexname on mytable(username(length));    

If it is a Char,varchar type, length can be less than the actual length of the field, and length must be specified if it is a blob and text type.

Modify table structure (add index)

[IndexName](username(length))      

Specify directly when creating a table

CREATE TABLE mytable(   ID INT not null,    username VARCHAR(+) not null, [indexname](username(length)));       

Syntax for dropping an index

[IndexName] on mytable;   

Unique index

It is similar to the previous normal index, except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:

Create an index

CREATE UNIQUE INDEX indexname on mytable(username(length))    

Modify Table Structure

[IndexName](username(length))      

Specify directly when creating a table

CREATE TABLE mytable(   ID INT not null,    username VARCHAR(+) not null, [indexname](username(length)));       

To add and remove indexes by using the ALTER command

There are four ways to add an index to a data table:

• ALTER TABLE tbl_name add PRIMARY key (column_list): The statement adds a primary key, which means that the index value must be unique and cannot be null.

• ALTER TABLE tbl_name ADD unique index_name (column_list): This statement creates an indexed value that must be unique (except for NULL, NULL may occur more than once).
• ALTER TABLE tbl_name Add index index_name (column_list): Add a normal index and the index value can occur more than once.
• ALTER TABLE tbl_name ADD fulltext index_name (column_list): The statement specifies that the index is fulltext for full-text indexing.

The following example adds an index to the table.

MySQL>(c);   

You can also use the drop clause in the ALTER command to delete an index. Try the following instance to delete the index:

MySQL> ALTER TABLE testalter_tbl DROP INDEX c;  

To add and remove primary keys by using the ALTER command

The primary key can only work on one column, and when you add a primary key index, you need to make sure that the primary key is not empty by default (not NULL). Examples are as follows:

MySQL> ALTER TABLE testalter_tbl MODIFY i INT not NULL;  MySQL>(i);       

You can also delete a primary key by using the ALTER command:

MySQL> ALTER TABLE testalter_tbl DROP PRIMARY KEY;  

You only need to specify primary key when you delete the primary key, but you must know the index name when you delete the index.

Display index Information

You can use the SHOW Index command to list related index information in a table. You can format the output information by adding \g.

Try the following examples:

MySQL> SHOW INDEX from table_name; \g   

Http://www.runoob.com/mysql/mysql-index.html

Database-mysql Index