MySQL adds an index to a table

Index function

In addition to the ordered lookups mentioned above, the database uses a variety of fast location techniques to greatly improve the query efficiency. In particular, when the amount of data is very large and the query involves more than one table, using an index can often speed up the query by tens of thousands of times.

For example, there are 3 unindexed tables T1, T2, T3, each containing only columns C1, C2, and C3, each containing 1000 rows of data, referring to the value of 1~1000, and the query that looks for the equivalent row of 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 with 3 equal values per row. To process this query without indexing, you must look for all the combinations of 3 tables in order to derive those rows that match the WHERE clause. The number of possible combinations is 1000x1000x1000 (1 billion), and obviously the query will be very slow.

If you index each table, you can greatly speed up the query process. Queries using the index are handled as follows.

(1) Select the first row from the table T1 to see the data that this row contains.
(2) Use the Index on table T2 to directly locate the row in the T2 that matches the value of T1. Similarly, use the index on table T3 to directly locate rows in T3 that match the values from T1.
(3) Scan the next line of the table T1 and repeat the previous procedure until all the rows in the T1 are traversed.

In this case, a full scan is still performed on table T1, but the ability to index lookups on tables T2 and T3 directly takes rows from those tables, 1 million times times faster than unused indexes.
Using the index, MySQL accelerates the search where the clause satisfies the criteria row, while in a multi-table join query, it speeds up matching rows in other tables when the connection is executed.

1. Add primary key (primary key index)
Mysql>alter TABLE ' table_name ' ADD PRIMARY KEY (' column ')
2. Add unique (unique index)
Mysql>alter TABLE ' table_name ' ADD UNIQUE (
' Column '
)
3. Add index (normal index)
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column ')
4. Add fulltext (full-text index)
Mysql>alter TABLE ' table_name ' ADD fulltext (' column ')
5. Adding Multi-column indexes
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column1 ', ' column2 ', ' column3 ')

Here's a more detailed approach

In MySQL, you can use the ALTER TABLE SQL statement to add an index to a field in a table.

The basic syntax for adding an index to a field in a table by using the ALTER TABLE statement is:
ALTER table < table name > ADD INDEX (< field >);

Let's try adding an index to the T_name field in test.

Mysql> ALTER TABLE test add index (T_NAME);
Query OK, 0 rows affected (0.17 sec)
records:0 duplicates:0 warnings:0

After successful execution, let's take a look at the results.

mysql> describe test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_id | Int (11) |     YES | |       NULL | |
| T_name | varchar (50) | NO | MUL |       NULL | |
| T_password | char (32) |     YES | |       NULL | |
| T_birth | Date |     YES | |       NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in Set (0.00 sec)

As you can see, the key column of the T_name field is changed from the original blank to the Mul. What does this mul mean? A simple explanation: If key is Mul, then the value of the column can be repeated, which is a leading column of a non-unique index (the first column) or a component of a uniqueness index but can contain null values.

2. Create an index

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

1. ALTER TABLE

ALTER table is used to create a normal index, a unique index, or a primary key index.

?

123	alter table table_name add index index _name (column_list) alter table table_name add unique Code class= "SQL Plain" > (column_list) alter table table_name add PRIMARY key (column_list)

Where table_name is the name of the table to increase the index, column_list indicates which columns to index, and columns are separated by commas. Index name index_name optional, by default, MySQL assigns a name based on the first indexed 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.

2. CREATE INDEX

CREATE Index to add a normal or unique index to a table.

?

12	CREATEINDEX index_name ON table_name (column_list)CREATE UNIQUE INDEX index_name ONtable_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 selectable. In addition, the primary key index cannot be created with the CREATE INDEX statement.

3. Index type

When you create an index, you can specify whether the index can contain duplicate values. If not included, the index should be created as a primary key or a unique index. For single-column uniqueness indexes, this guarantees that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is guaranteed to be distinct.

The PRIMARY key index is very similar to a unique index. In fact, the PRIMARY key index is only a unique index with the name PRIMARY. This means that a table can contain only one primary KEY because it is not possible 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 the SID.

Copy CodeThe code is as follows: ALTER TABLE students ADD PRIMARY KEY (SID)

4. Deleting 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 handled as a statement inside ALTER TABLE, with the following syntax.

?

123	drop index index_name on talbe_name alter table table_name drop index index_name alter table table_name drop primary key

Where the first two statements are equivalent, delete the index index_name in table_name.
The 3rd statement is only used when deleting the primary key index, because a table may have only one primary key index, so 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 removed from the table, the index is affected. For multiple-column combinations of indexes, if one of the columns is deleted, the column is also removed from the index. If you delete all the columns that make up the index, the entire index is deleted.

5. View Index

?

12	mysql> show indexfrom tblname;mysql> show keys fromtblname;

· Table
The name of the table.
· Non_unique
0 if the index cannot include a repeating word. 1 if it is possible.
· Key_name
The name of the index.
· Seq_in_index
The column sequence number in the index, starting at 1.
· column_name
The column name.
· Collation
The column is stored in the index in what way. In MySQL, there is a value of ' A ' (ascending) or null (no classification).
· Cardinality
An estimate of the number of unique values in the index. You can update by running analyze table or myisamchk-a. The cardinality is counted according to the statistics stored as integers, so even for small tables, this value is not necessarily accurate. The larger the cardinality, the greater the chance that MySQL will use the index when it is federated.
· Sub_part
The number of characters that are indexed if the column is only partially indexed. Null if the entire column is indexed.
· Packed
Indicates how the keyword is 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 indexed methods (BTREE, Fulltext, HASH, RTREE).
· Comment

Add: For SQL statement view and processing, this site also provides the SQL statement online Format beautification tool for everyone to refer to the use of:

SQL code online format beautification tool : Http://tools.jb51.net/code/sqlcodeformat

MySQL adds an index to a table