MySQL index

The establishment of MySQL index is very important for the efficient operation of MySQL, and index can greatly improve the retrieval speed of MySQL.

For example, if a well-designed and indexed MySQL is a Lamborghini, a MySQL without an index is a man-made tricyle.

Take the catalog page (index) of the Chinese dictionary for example, we can quickly find the required words by pinyin, stroke, side-by-side first-class sort of directory (index).

Indexes are column-by-column and composite indexes. A single-column index, that is, an index contains only a single column, and a table can have multiple single-column indexes, but this is not a composite index. A composite index, that is, an index contains more than one column.

When you create an index, you need to make sure that the index is a condition that is applied to the SQL query statement (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 the records of the entity table.

The benefits of using indexes are all talked about above, but excessive use of indexes can lead to abuse. I ndexes therefore have their drawbacks: while indexes greatly increase query speed, they also slow down the updating of tables, such as INSERT, UPDATE, and DELETE. Because when you update a table, MySQL saves not only the data, but also the index file.

Indexed index files that take up disk space.

Normal index

Create an index

This is the most basic index, and it has no restrictions. It can be created in several ways:

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

In the case of CHAR, VARCHAR type, length can be less than the actual length of the field, and in the case of BLOB and TEXT types, length must be specified.

Modifying the table structure (adding an index)

ALTER table tableName ADD INDEX indexName(columnName)

Specify directly when you create a table

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

The syntax for deleting the index

DROP INDEX [indexName] ON mytable; 

Unique index

It is similar to the previous normal index, unlike the value of the index column, which must be unique, but allow for empty values. I f the index is combined, the combination of column values must be unique. It can be created in several ways:

Create an index

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

Modify the table structure

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

Specify directly when you create a table

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

Use the ALTER command to add and remove indexes

There are four ways to add an index of 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 not NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): The value of this statement to create an index must be unique (NULL may occur multiple times except for NULL).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Add a normal index, and the index value can appear multiple times.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): This statement specifies the index as FULLTEXT for full-text indexing.

The following example is to add an index to the table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can also use the DROP clause in the ALTER command to remove the index. Try removing the index from the following instance:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Use the ALTER command to add and remove primary keys

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). Here's an example:

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 remove the primary key:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

The primary key only needs to be specified when the PRIMARY KEY is deleted, but you must know the index name when you delete the index.

The index information is displayed

You can use the SHOW INDEX command to list the relevant index information in the table. You can format the output information by adding a .G.

Try the following example:

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