MySQL database index type/create/use combination ALTER statement usage in MySQL

MySQLindex type/create/use combo alter inMySQLcommand statement usage

MySQL indexes

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

For example, if a properly designed and indexed MySQL is a Lamborghini, then MySQL without an indexed design is a human tricycle.

The index is divided into single-column index and composite index.Single-column index, that is, an index contains only a single column, a table can have multiple single-column indexes, but this is not a composite index.Composite index, that is, one index contains multiple columns.

When creating an index, you need to make sure that the index is a condition that applies to the SQL query (usually as a condition of the WHERE clause).

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

The above are all about the benefits of using indexes, but excessive use of indexes will lead to abuse.Therefore, the index will also have its shortcomings: although the index greatly improves the query speed, it will reduce the speed of updating the table, such as INSERT, UPDATE and DELETE on the table.Because when updating the table, MySQL not only saves the data, but also saves the index file.

Indexing an index file that consumes disk space.

normal index

Create index

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

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

For CHAR and VARCHAR types, length can be less than the actual length of the field; for BLOB and TEXT types, length must be specified.

Modify table structure (add index)

ALTER table tableName ADD INDEX indexName(columnName)

Specify directly when creating the table

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

Syntax to drop an index

DROP INDEX [indexName] ON mytable; 

unique index

It is similar to the previous ordinary index, the difference is: the value of the index column must be unique, but null values ​​are allowed.In the case of a composite index, the combination of column values ​​must be unique.It can be created in the following ways:

Create index

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

Modify table structure

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

Specify directly when creating the table

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

Add and remove indexes 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): This statement adds a primary key, which means that index values ​​must be unique and cannot be NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): The value of the index created by this statement must be unique (except NULL, NULL may appear multiple times).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Add a normal index, the index value can appear multiple times.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):The statement specifies the index as FULLTEXT for full-text indexing.

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

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can also use the DROP clause on the ALTER command to drop indexes.Try the following instance to drop the index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Add and remove primary keys using the ALTER command

The primary key can only act on one column. When adding a primary key index, you need to ensure that the primary key is not NULL by default (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 delete a primary key with the ALTER command:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

You only need to specify the PRIMARY KEY when dropping the primary key, but when dropping an index, you must know the index name.

show index information

You can use the SHOW INDEX command to list the relevant index information in the table.The output information can be formatted by adding \G.

Try the following examples:

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