Q:

Why mysql query does not use index

In MySQL 5.xxx, I ran the query below and it showed that no index was used although there was a possible key. 
explain
select * from OPER_MEMB_AAAB
where AAA between '2020/01/01' and '2020/03/22'
order by AAA;

And here is my table structure. 
create table OPER_MEMB_AAAB
(BAG varchar(16) ,
BBJ varchar(4) ,
AAA datetime ,
AAB int ,
AAC smallint ,
AAD int comment ,
AAE smallint ,
AAF datetime )
create unique index IDX_OPER_MEMB_AAAB_U on OPER_MEMB_AAAB(AAA,BAG,BBJ,AAC);
create index IDX_OPER_MEMB_AAAB_1 on OPER_MEMB_AAAB(AAF);

AAA is a datetime column and the first column in unique index. Why Mysql did not use the unique index although it knews it could use it? This table has 4 million records and 2.8 million distinct AAA values.

A:

The Optimizer looks at the indexes but may fall back to simply scanning the table. 
 If the index is selective, it will use the index; if not it will probably be faster to scan the table, thereby avoiding the bouncing back and forth between the index's BTree and data's BTree.
The cutoff varies, but a convenient Rule of Thumb is 20%.  2.8M / 4M is far more than 20%, so it chose to scan the table.  Shrink the date range, you will see the Explain switch to using the index.

