mysql The index is used in two ways after the index is created:
1 is automatically determined by the query optimizer of the database to use the index;
2 users can force the index to be used when writing SQL statements
The following is a description of how the two indexes are used
The first, the index is automatically used. When a query statement is received, the database looks at the query criteria following the WHERE statement, while looking at which indexes are on the table and then matching based on the query criteria and index. The matching of the
query criteria and index includes the match of the query field to the indexed field and the query type and index type. The former is very well understood, that is, the properties of the query condition to be indexed, the latter is that the query condition must be able to use the index, such as the equivalent judgment and range query can use B + Tree index, and the hash index can only be applied to the equivalent judgment.
After finding the index matching the query criteria, is to make a cost estimate to determine whether to use the index, the cost estimate is mainly based on the number of accesses to access, generally speaking, if the number of records accessed through the index accounted for more than 15% of the total table records, then the index is not used instead of using full table scan. Because the cost of using the index at this time is greater. Using indexes in most cases is more efficient. The
is determined by the optimizer and ultimately determines whether to use the index
Second, forcing the use of the index, primarily through SQL statements
Select * FROM Table Force index (PRI) limit 2; (forcing primary key)
SELECT * FROM Table Force index (ZIDUAN1_INDEX) limit 2; (Enforce use of index "Ziduan1_index")
Select * FROM Table Force index (Pri,zidu An1_index) limit 2; (Enforce use of index "PRI and Ziduan1_index")
can also suppress the use of indexes
SELECT * FROM table Ignore index (PRI) limit 2; (Prohibit use of primary key) Br>select * FROM table Ignore index (ZIDUAN1_INDEX) limit 2; (Prohibit use of index "Ziduan1_index")
Select * FROM table Ignore index (PRI , ziduan1_index) limit 2; (Prohibit use of index "Pri,ziduan1_index")
MySQL is automatically queried from the index when it is queried by MySQL after adding the index. Or is there a separate parameter query index when querying?