Organize MySQL indexes

MySQL index

The establishment of MySQL index is very important for the efficient operation of MySQL. The index can be greatly improved. MySQL retrieval speed.

For example, if MySQL that is properly designed and uses indexes is a Lamborghini, then MySQL that is not designed and uses indexes is a human tricycle.

Take the table of contents page (index) of a Chinese dictionary as an example. We can quickly find the words we need through the table of contents (index) sorted by pinyin, strokes, radicals, etc.

Indexes are divided into single column indexes and combined indexes.

Single column index, that is, an index only contains a single column. A table can have multiple single column indexes, but this is not a combined index.

Combined index, that is, an index contains multiple columns.

When creating an index, you need to ensure that the index is a condition applied to the SQL query statement (usually as a condition of the WHERE clause).

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

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

Creating index files will occupy disk space. The second drawback of occupying resources

Related free learning recommendations:mysql video tutorial

Normal index

The most basic index type, without restrictions such as uniqueness.

Create index

This is the most basic index, it has no restrictions. It has the following creation methods:

CREATE INDEX indexName ON table_name (column_name)

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

Modify the 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))  
 );

Delete index The syntax of

DROP INDEX [indexName] ON mytable;

Unique Index

A unique index is one that does not allow any two rows to have the same index value.

Most databases do not allow newly created unique indexes to be saved with the table when there are duplicate key values ​​in the existing data. The database may also prevent adding new data that would create duplicate key values ​​in the table. For example, if you create a unique index on an employee's last name (lname) in the employee table, no two employees can have the same last name.

Create index

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

Modify the 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))  
 );

Use the ALTER command to add and delete indexes

There are four ways to add indexes to the 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 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 above is the detailed content of Organize MySQL indexes. For more information, please follow other related articles on the PHP Chinese website!