Shulou(Shulou.com)05/31 Report--

How to create and delete indexes in MySQL? for this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

1. Index function

In the index column, in addition to the ordered search mentioned above, the use of a variety of fast positioning techniques can greatly improve the query efficiency. Especially when the amount of data is very large and the query involves multiple tables, the use of indexes can often speed up the query by thousands of times.

For example, there are three unindexed tables T1, T2, and T3 that contain only columns C1, c2, and c3, each with 1000 rows of data, indicating a value of 1 to 1000. The query for finding rows with equal values is shown below.

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

The result of this query should be 1000 rows, each containing 3 equal values. To process this query without an index, you must find all the combinations of the three tables in order to get those rows that match the WHERE clause. The number of possible combinations is 1000 × 1000 × 1000 (billion), so it is clear that the query will be very slow.

If each table is indexed, the query process can be greatly accelerated. The query processing using the index 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 in T2 that matches the value of T1. Similarly, use the index on table T3 to directly locate rows in T3 that match the value from T1.

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

In this case, a full scan of table T1 is still performed, but the rows in these tables can be fetched directly by index lookup on tables T2 and T3, which is 1 million times faster than when the index is not used.

Using the index, MySQL speeds up the search for rows that satisfy the criteria of the WHERE clause, while in a multi-table join query, it speeds up the speed of matching rows in other tables when performing joins.

two。 Create an index

You can create an index when you execute a CREATE TABLE statement, or you can add an index to a table using CREATE INDEX or ALTER TABLE alone.

1.ALTER TABLE

ALTER TABLE is used to create normal, UNIQUE, or PRIMARY KEY indexes.

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)

Where table_name is the name of the table to be indexed, column_list indicates which columns are indexed, and multiple columns are separated by commas. The index name index_name is optional, and by default, MySQL assigns a name based on the first index column. In addition, ALTER TABLE allows multiple tables to be changed in a single statement, so multiple indexes can be created at the same time.

2.CREATE INDEX

CREATE INDEX can add a normal index or an UNIQUE index 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 in the ALTER TABLE statement, and the index name is not optional. In addition, PRIMARY KEY indexes cannot be created with CREATE INDEX statements.

3. Index type

When you create 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 a single column uniqueness index, this ensures that the single column does not contain duplicate values. For multi-column unique indexes, the combination of multiple values is guaranteed not to repeat.

PRIMARY KEY indexes are very similar to UNIQUE indexes. In fact, the PRIMARY KEY index is just an UNIQUE index with the name PRIMARY. This means that a table can contain only one PRIMARY KEY, because it is impossible to have two indexes with the same name in a table.

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

ALTER TABLE students ADD PRIMARY KEY (sid)

4. Delete index

You can use ALTER TABLE or DROP INDEX statements to delete the index. Similar to the CREATE INDEX statement, DROP INDEX can be treated as a statement within ALTER TABLE, with the following syntax.

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, deleting the index index_name in table_name.

The third statement is used only when deleting an PRIMARY KEY index, because a table can only have one PRIMARY KEY index, so there is no need to specify an index name. If no PRIMARY KEY index is 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. For an index with a combination of columns, if you delete one of the 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 the index

> show index from tblname

Mysql > show keys from tblname

Table

The name of the table.

Non_unique

0 if the index cannot include duplicate words. 1, if possible.

Key_name

The name of the index.

Seq_in_index

The column sequence number in the index, starting at 1.

Column_name

Column name.

Collation

How the column is stored in the index. In MySQL, there is a value of'A'(ascending order) or NULL (no classification).

Cardinality

An estimate of the number of unique values in the index. It can be updated by running ANALYZE TABLE or myisamchk-a. The cardinality is counted based on statistics stored as integers, so even for small tables, the value does not need to be accurate. The higher the cardinality, the greater the chance that MySQL will use the index when federating.

Sub_part

If the column is only partially indexed, the number of characters indexed. NULL if the entire column is indexed.

Packed

Indicates how keywords are compressed. NULL if it is not compressed.

Null

If the column contains NULL, it contains YES. If not, the column contains NO.

Index_type

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

Comment

This is the answer to the question about how to create and delete indexes in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.