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

The following content mainly brings you the meaning and value of mysql index. the knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

The meaning of the existence of index:

In general, after the index is added, the speed of accessing database tables can be improved, and the query will be faster.

It is more beneficial to improve the performance of select statements.

Don't over-index.

It is not as long as indexing will improve performance, it may be counterproductive.

What kind of field is suitable for creating an index? in general:

Primary key

Columns that often need to be sorted

Columns that are often used in where clauses

What kind of fields are not suitable for creating indexes? in general:

Columns with few data values (for example, gender, only 2 values, either male or female)

Field types text, image, bit (these are all large fields with a large amount of data)

When the modification is much larger than the search (more update operations, less select operations)

Several different indexes:

Primary key index

Rule: index columns cannot contain duplicate values and cannot be empty

Alter table Table 1 add primary key (column 1 [, column 2])

General index

No rules

Alter table Table 1 add index Index name (column 1 [, column 2])

Create index Index name on Table 1 (column 1 [, column 2])

For example:

Alter table person_info add index myindex (salary)

Alter TABLE person_info add index hisindex (name, salary)

Create index yourindex on person_info (salary)

Create index herindex on person_info (salary, name)

Unique index

Rule: the value of the index column must be unique, but can be empty

Alter table Table 1 add unique index Index name (column 1 [, column 2])

Create unique index Index name on Table 1 (column 1 [, column 2])

View Index

Show index from Table 1

Desc Table 1 can also be seen by looking at the table structure.

For example:

Show index from person_info

Desc Table 1

Deletion of index

Drop index Index name on Table 1

Drop index yourindex on person_info

Single index and combined index

Single index:

Alter table person_info add index myindex (salary)

Alter table person_info add index myindex (name)

Combinatorial index

Alter TABLE person_info add index hisindex (name, salary)

Remarks:

The efficiency of combined index is higher than that of single index, but it is also related to the amount of data.

For the significance and value of the above mysql index, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.