Are mysql indexes used automatically?

Mysql index can be used automatically, and users can also force it to be used. MYSQL uses the index in two ways after creating the index: one is that the query optimizer of the database automatically determines whether to use it; the other is that the user can force the use of the index when writing SQL statements.

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

(Recommended tutorial: mysql video tutorial)

There are two ways for MYSQL to use the index after creating the index:

1. The query optimizer of the database automatically determines whether to use an index;

2. Users can force the use of indexes when writing SQL statements.

The following two indexes are used Ways to explain

The first method is to automatically use the index.

After receiving the query statement, the database will check the query conditions behind the where statement, and also check what indexes are on the table, and then match them based on the query conditions and indexes.
The matching of query conditions and indexes includes the matching of query fields and index fields and the matching of query types and index types. The former is easy to understand, that is, an index must be built on the attributes of the query conditions, while the latter means that the query conditions must be able to use indexes. For example, equal value judgment and range query can use B-tree index, while hash index can only be applied to equal values. judge.

After finding an index that matches the query conditions, a cost estimate is made 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 the total table records If the number is more than 15%, the index will not be used but a full table scan will be used, because the cost of using the index is greater at this time. Using indexes will improve efficiency in most cases.

After the optimizer’s judgment, it will ultimately decide whether to use the index.

The second method is to force the use of indexes, which is mainly achieved through SQL statements.

select * from table force index(PRI) limit 2;(强制使用主键)
select * from table force index(ziduan1_index) limit 2;(强制使用索引"ziduan1_index")
select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引"PRI和ziduan1_index")

You can also prohibit the use of indexes

select * from table ignore index(PRI) limit 2;(禁止使用主键)
select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引"ziduan1_index")
select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引"PRI,ziduan1_index")

The above is the detailed content of Are mysql indexes used automatically?. For more information, please follow other related articles on the PHP Chinese website!