Shulou(Shulou.com)06/01 Report--

Indexes

An index is a structure that sorts the values of one or more columns in a database table. If you want to find a particular employee by his or her last name, the index helps you get information faster than searching all the rows in the table.

Without an index, executing the statement select * from test_info where id=1000; will search from the first row of the table to the 1000th row, and after you add an index to this column, you will quickly locate this field through the index.

Classification of the index:

The main results are as follows: 1. Clustered index can improve the speed of multi-row retrieval, while non-clustered index can retrieve single row quickly.

2. Unique index: two rows are not allowed to have the same value

3. Primary key index: defining a primary key for a table automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key is unique and cannot be empty.

4. General index: there are no restrictions.

Creation of the index:

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 that this statement creates an index must be unique (NULL may appear multiple times except for NULL).

ALTER TABLE tbl_name ADD INDEX index_name (column_list): add a normal index, where the index value can appear multiple times.

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): this statement specifies that the index is FULLTEXT for full-text indexing.

Deletion of index:

ALTER TABLE tb1_name DROP INDEX index_name

Use show index from tablename; to query indexes that have been set.

The following is an example of adding, deleting and viewing an index: (under my_sql)

Summary:

After creating the index, it can not only speed up the retrieval speed, but also sort the retrieved data.

There are advantages and disadvantages. Creating an index consumes space and slows down the loading of data, so don't be redundant to add an index.

Proper addition will greatly increase the efficiency of database query.

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.