About MySQL index automatic optimization mechanism: Index selectivity (cardinality: index cardinality)

1, two the same structure of the statement one does not use the index of the problem: 1 to 20th no index, check 1 to 5th is indexed, why? Not stable?  mysql> Explain select * from Test where f_submit_time between ' 2009-09-01 ' and ' 2009-09-20 ' \g; ************ 1. Row ***************************           id:1  select_type:simple    &nbs P   table:test         type:ALLpossible_keys:PRIMARY,submit_time_index          key:null      key_len:null          ref:null         rows:365628        extra:using where1 row in Set (0.02 sec)   mysql> Explain select * from Test where f_submit_time between ' 2009-09-01 ' and ' 2009-09-5 ' \g;  *************************** 1. Row ***************************           id:1  select_type:simple    &nbs P   table:test         TYPE:RANGEPOSSIBLe_keys:primary,submit_time_index          key:submit_time_index      Key_len:8           ref:null         rows:52073        Extra : Using where1 row in Set (0.00 sec)    Description: Two fork tree index the most suitable is the point query, and a small range of the range query, when the estimated amount of data returned more than a certain percentage (seemingly when the estimated amount of queries reached 30% of the total) , and then according to the index one by one to check the slow, rather than the full table scan faster. MySQL has its own internal automatic optimization mechanism, but some automatic optimization mechanisms may not be optimal. This is the time to intervene manually. For example, long-term non-optimal table, MySQL to determine the index is not optimal, will not use the index. It is sometimes necessary to manually enforce a truly efficient index (force index).  

In fact, when its own query is about equal to a full table query, strong does not enforce the use of the index is basically no effect.

2. Look again at an example:

Today, there is a strange problem, obviously has established the index, the SELECT statement explain also indicates that the index will be used, but the result is not indexed, and finally scanned the whole table.
Two SQL statements with exactly the same structure:

Sql1:select * FROM table where col_a = 123 and Col_b in (' foo ', \ ' bar ') the ORDER by id DESC;

Sql2:select * FROM table where col_a = 456 and Col_b in (' foo ', \ ' bar ') the ORDER by id DESC;

As a result, SQL1 chose to take advantage of the Col_a index, and the SQL2 used the index of the primary key ID to scan the full table (40w lines).
Careful analysis, found that the database, col_a=456 record number of nearly 10,000, and col_a=123 records only a few.
So it became clear that the MySQL selection index was based not only on the query structure and the index structure, but also on estimating the amount of data for each index based on the index, and then selecting the index that he thought was the fastest.
It is possible that the primary key index will be faster than normal index, so MySQL finally chooses the data volume with the large ID index.
So, how to solve this problem?
Very simple, just write multiple keys in the order statement, for example: ORDER by col_a, id DESC

The mechanism of using indexes in Ref:mysql queries HTTP://BLOGREAD.CN/IT/ARTICLE/5023?F=WB

3, the essence Reason: cardinality (index Base)

A key parameter, the average value group = Index cardinality/table total data row, the closer the average value group is to 1, the more likely it is to take advantage of the index.

Index selectivity is the ratio of non-repeating index values to the number of data rows in the cardinality (cardinality) table, index selectivity = cardinality/data rows, and cardinality can be viewed by "Show index from table name".
The advantage of high index selectivity is that when MySQL finds matches, it can filter more rows, the selectivity of a unique index is the best, and the value is 1.

4. For MySQL index optimization and use, see:

Explore MySQL index structure principle, performance analysis and optimization

http://my.oschina.net/leejun2005/blog/73912

About MySQL index automatic optimization mechanism: Index selectivity (cardinality: index cardinality)