When MySQL is added to the index, MySQL automatically queries the index when it is queried. Or is there a separate parameter query index when querying?

Source: Internet
Author: User

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?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.