Whether the mysql index can be used automatically

This article mainly introduces whether the mysql index can be used automatically, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand whether the mysql index can be used automatically.

After creating the index, MYSQL uses the index in two ways: first, the query optimizer of the database automatically determines whether to use the index; second, users can force the use of the index when writing SQL statements.

After creating the index, MYSQL uses the index in two ways:

1 the query optimizer of the database automatically determines whether to use the index or not

2 users can force the use of indexes when writing SQL statements

Here are two ways to use the index

First, the index is used automatically.

After receiving the query statement, the database will look at the query conditions after the where statement, see which indexes are on the table, and then match them according to the query conditions and indexes.

The matching of query condition and index includes the matching of query field and index field and the matching of query type and index type. The former is easy to understand, that is, an index should be built on the attributes of the query condition, while the latter means that the query condition must be able to use indexes, such as equivalence judgment and range query can use B + tree index, while hash index can only be applied to equivalence judgment.

After finding the index that matches the query condition, the cost estimate is carried out to decide whether to use the index. The cost estimate is mainly based on the number of records to be accessed. Generally speaking, if the number of records accessed through the index accounts for more than 15% of the total number of records in the table, instead of using the index, the full table scan will be used, because it is more expensive to use the index at this time. Using indexes is more efficient in most cases.

After the judgment of the optimizer, it will finally decide whether to use the index.

Second, force the use of indexes, mainly through SQL statements

Select * from table force index (PRI) limit 2; (force primary key)

Select * from table force index (ziduan1_index) limit 2; (force index "ziduan1_index")

Select * from table force index (PRI,ziduan1_index) limit 2; (enforce indexes "PRI and ziduan1_index")

You can also prohibit the use of indexes.

Select * from table ignore index (PRI) limit 2; (primary key prohibited)

Select * from table ignore index (ziduan1_index) limit 2; (index "ziduan1_index" is prohibited)

Select * from table ignore index (PRI,ziduan1_index) limit 2; (index "PRI,ziduan1_index" is prohibited)

After reading the above about whether the mysql index can be used automatically, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.