Latest web development tutorials

MySQL Index

MySQL MySQL build indexes for efficient operation is very important, indexes can greatly improve the retrieval speed of MySQL.

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

Index points separate index and combination index. Separate index that contains only a single column index, a table can have multiple separate index, but this is not a composite index. Composite index, namely a cable comprising a plurality of columns.

When you create an index, you need to ensure that the index is used in an SQL query conditions (generally as a condition of the WHERE clause).

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

The above are talking about the benefits of using the index, but the excessive use of the index will result in abuse. Therefore, the index also has its disadvantages: While the index greatly increased query speed, while it will reduce the speed of updating the table, such as table INSERT, UPDATE, and DELETE. Because the update table, MySQL is not only to save the data, but also save about index file.

Indexing takes up disk space in the index file.

General index

Create Index

This is the basic index, it does not have any restrictions. It has created the following ways: 

CREATE INDEX indexName ON mytable(username(length));

If it is CHAR, VARCHAR type, length can be less than the actual length of the field; if BLOB and TEXT types, you must specify the length.

Modify Table Structure 

ALTER mytable ADD INDEX [indexName] ON (username(length))

When you create a table directly specify 

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

Delete index syntax 

DROP INDEX [indexName] ON mytable;

Unique Index

It is similar to the previous general index, it is different: the value of the index columns must be unique, but allow free value. If it is a combination of the index, the column value must be unique. It has created the following ways:

Create Index 

CREATE UNIQUE INDEX indexName ON mytable(username(length))

Modify Table Structure 

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

When you create a table directly specify 

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);

Use ALTER command to add and delete indexes

There are four ways to add index data table:

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list ): This statement adds a primary key, which means that the index value must be unique and can not be NULL.

  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list ): This statement creates an index value must be unique (except outside NULL, NULL may appear multiple times).
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list ): add a normal index, the index value can occur multiple times.
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list ): This statement specifies the index is FULLTEXT, for full-text indexing.

The following examples add an index in the table. 

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can also use the DROP clause to remove the index in the ALTER command. Try the following examples to delete the index: 

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

Use ALTER command to add and delete the primary key

Only act on a primary key column, when you add a primary key index, you need to make sure that the default primary key is not empty (NOT NULL). Examples are as follows: 

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

You can also use the ALTER command to delete the primary key: 

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Just specify PRIMARY KEY Delete the specified time, but when you delete the index, you must know the name of the index.

Display index information

You can use the SHOW INDEX command to list the table index information. By adding \ G to format the output.

Try the following examples: 

mysql> SHOW INDEX FROM table_name\G
........