Create, delete, and view MySQL indexes _ MySQL

Source: Internet
Author: User
Create, delete, and view the index of bitsCN. com1.
In addition to the ordered search mentioned above, the database can greatly improve the query efficiency by using various fast locating technologies. In particular, when the data volume is very large and the query involves multiple tables, using indexes often speeds up the query by thousands of times.

For example, three unindexed tables t1, t2, and t3 contain only columns c1, c2, and c3. each table contains 1000 rows of data, which is 1 ~ The value of 1000 is as follows.

SELECT c1, c2, c3 FROM t1, t2, t3 WHERE c1 = c2 AND c1 = c3

The query result should be 1000 rows. each row contains three equal values. To process this query without an index, you must search for all the combinations of the three tables to obtain the rows that match the WHERE clause. The possible number of combinations is 1000x1000x1000 (billions), and the query will obviously be very slow.

If you index each table, the query process can be greatly accelerated. The index query process is as follows.

(1) select the first row from table t1 to view the data contained in this row.

(2) use the index on table t2 to directly locate the row that matches the value of t1 in table t2. Similarly, you can use the indexes on table t3 to directly locate the rows in table t3 that match the values from t1.

(3) scan the next row of table t1 and repeat the previous process until all the rows in table t1 are traversed.

In this case, a full scan is still performed on table t1, but the rows in these tables can be directly retrieved through index search on table t2 and table t3, which is 1 million times faster than when no index is used.

Using indexes, MySQL accelerates the WHERE clause's search for rows that meet the condition conditions. in multi-table join queries, MySQL accelerates row matching in other tables during join execution.

2. create an index
You can CREATE an INDEX when executing the create table statement, or use the create index or alter table statement to add an INDEX to the TABLE.

Alter table is used to create a common index, a UNIQUE index, or a primary key index.

Alter table table_name add index index_name (column_list)

Alter table table_name add unique (column_list)

Alter table table_name add primary key (column_list)

Table_name is the name of the table to be indexed, and column_list indicates which columns are indexed. when multiple columns are indexed, they are separated by commas. The index name index_name is optional and is time-saving. MySQL assigns a name based on the first index column. In addition, alter table allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.

Create index can add normal or UNIQUE indexes to a table.

Create index index_name ON table_name (column_list)

Create unique index index_name ON table_name (column_list)

Table_name, index_name, and column_list have the same meaning as the alter table statement, and the index name is not optional. In addition, you cannot use the create index statement to CREATE a primary key index.

3. index type
When creating an index, you can specify whether the index can contain duplicate values. If not, the index should be created as a primary key or UNIQUE index. For single-column uniqueness indexes, this ensures that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is not repeated.

The primary key index is very similar to the UNIQUE index. In fact, the primary key index is only a UNIQUE index with the name PRIMARY. This indicates that a table can only contain one primary key, because a table cannot have two indexes with the same name.

The following SQL statement adds the PRIMARY KEY index to the students table on the sid.

Alter table students add primary key (sid)

4. delete an index
You can use the alter table or drop index statement to delete an INDEX. Similar to the create index statement, drop index can be processed as a statement in alter table. The syntax is as follows.

Drop index index_name ON talbe_name

Alter table table_name drop index index_name

Alter table table_name DROP PRIMARY KEY

The first two statements are equivalent. The index index_name in table_name is deleted.

The first statement is only used to delete the primary key index. because a table only has one primary key index, you do not need to specify the index name. If the primary key index is not created, but the table has one or more UNIQUE indexes, MySQL deletes the first UNIQUE index.

If a column is deleted from the table, the index is affected. If you delete a column in an index with multiple columns, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

5. View indexes

Mysql> show index from tblname;

Mysql> show keys from tblname;

· Table

Table name.

· Non _ unique

If the index cannot contain duplicate words, the value is 0. If yes, it is 1.

· Key _ name

The name of the index.

· Seq _ in_index

The column serial number in the index, starting from 1.

· Column _ name

Column name.

· Collation

How the column is stored in the index. In MySQL, there are values 'A' (ascending) or NULL (unclassified ).

· Cardinality

The estimated number of unique values in the index. You can update analyze table or myisamchk-a by running analyze table. The base number is counted based on the statistical data stored as an integer. Therefore, this value is not required to be accurate even for small tables. The larger the base, the larger the chance for MySQL to use the index for Union.

· Sub _ part

If a column is partially indexed, it is the number of indexed characters. If the entire column is indexed, the value is NULL.

· Packed

Indicates how keywords are compressed. If it is not compressed, it is NULL.

· Null

If the column contains NULL, YES is included. If NO, the column contains NO.

· Index _ type

Used index methods (BTREE, FULLTEXT, HASH, RTREE ).

· Comment

From OO & XX's blog

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.