Mysql:mysql between date index problem-date index use

Mysql:mysql between date index problem-date index use

Table structure:

Dep_date

Dep

Arr

Federated indexes: Ind_coll_date_route (dep_date, Dep,arr)

These two days found that the original query efficiency is slow, using explain view, incredibly did not use the index,
My index is a date type, the first thought is MySQL on the date type index processing mechanism is not different, in the Where condition to try a few, found the effect is similar,

where Dep_date >= ' 20161121 '
where Dep_date >= ' 2016-11-21 '
where dep_date between ' 2016-11-01 ' and ' 2016-11-21 '

There are various functions, and finally found that the conclusion is the same, in Baidu Query also has no valuable content.

Finally, some laws were found, but it was not known whether there was universality, namely:

The index can be used when the number of query data is approximately one-fifth of the total number of bars, but when more than one-fifth is used, the full table is scanned.

Tried a number of tables, are this conclusion, so think this one-fifth should be set in a certain place, or the system default in the query optimization of an empirical value, in the future of the processing of learning to attract attention.

Workaround:

Forced Walk Index

SELECT * FROM  
Dep_datebetween '  2017-12-01 ' and  ' 2017-12-30 '

　　

Mysql:mysql between date index problem-date index use