Mysql sets up a federated index, but the index is not used after the time range is one more day.

1, date_point,corp_code,process_code
2, date_point in "2018-09-01" and "2018-10-17, walk the index, in" 2018-09-01 "and" 2018-10-18 "but not in the index

for the first comment, the amount of supplementary statistics (whether the statistics are correct)

explain select `corp_code`, `process_code`, count(distinct business_code) as total from `tra_aging_distributions`  
where `process_code` = "BAO_DAN_SHOU_LI" and `org_code` in ("JIAO_Y I_ZU_ZHI")  
and `corp_code` in ("FYGS8888", "TJBK8888", "LFBK8888", "DLBK8888", "NJBK8888", "WXBK8888", "HZBK8888", "HNFD0001", "WHABK8888", "GZBK8888", "XABK8888")  
and (`product_type_code` = "10001") 
and `date_point` between "2018-09-01" and "2018-10-17"  
and `cost_days` > "2" and `cost_days` <= "3"  
group by `corp_code`, `process_code`;

< table > < thead > < tr > < th > id < / th > < th > select_type < / th > < th > table < / th > < th > type < / th > < th > possible_keys < / th > < th > key < / th > < th > key_len < / th > < th > ref < / th > < th > rows < / th > < th > Extra < / th > < / tr > < / thead > < tbody > < tr > < td > 1 < / td > < td > SIMPLE < / td > < td > tra_aging_distributions < / td > < td > range < / td > < td > idx_date_corp_process < / td > < td > idx_date_corp_process < / td > < td > 587 < / td > < td > NULL < / td > < td > 1852870 < / td > < td > Using index condition; Using where; Using filesort < / td > < / tr > < / tbody > < / table >

1 row in set (0.01 sec)

explain select `corp_code`, `process_code`, count(distinct business_code) as total from `tra_aging_distributions`  
where `process_code` = "BAO_DAN_SHOU_LI" 
and `org_code` in ("JIAO_YI_ZU_ZHI")  
and `corp_code` in ("FYGS8888", "TJBK8888", "LFBK8888", "DLBK8888", "NJBK8888", "WXBK8888", "HZBK8888", "HNFD0001", "WHABK8888", "GZBK8888", "XABK8888")  
and (`product_type_code` = "10001") 
and `date_point` between "2018-09-01" and "2018-10-18"  
and `cost_days` > "2" and `cost_days` <= "3"  
group by `corp_code`, `process_code`;

< table > < thead > < tr > < th > id < / th > < th > select_type < / th > < th > table < / th > < th > type < / th > < th > possible_keys < / th > < th > key < / th > < th > key_len < / th > < th > ref < / th > < th > rows < / th > < th > Extra < / th > < / tr > < / thead > < tbody > < tr > < td > 1 < / td > < td > SIMPLE < / td > < td > tra_aging_distributions < / td > < td > ALL < / td > < td > idx_date_corp_process < / td > < td > NULL < / td > < td > NULL < / td > < td > NULL < / td > < td > 10540764 < / td > < td > Using where; Using filesort < / td > < / tr > < / tbody > < / table >

explain select `corp_code`, `process_code`, count(distinct business_code) as total from `tra_aging_distributions`  
where `process_code` = "BAO_DAN_SHOU_LI" and `org_code` in ("JIAO_Y I_ZU_ZHI")  
and `corp_code` in ("FYGS8888", "TJBK8888", "LFBK8888", "DLBK8888", "NJBK8888", "WXBK8888", "HZBK8888", "HNFD0001", "WHABK8888", "GZBK8888", "XABK8888")  
and (`product_type_code` = "10001") 
and `date_point` between "2018-10-18" and "2018-10-18"  
and `cost_days` > "2" and `cost_days` <= "3"  
group by `corp_code`, `process_code`;

< table > < thead > < tr > < th > id < / th > < th > select_type < / th > < th > table < / th > < th > type < / th > < th > possible_keys < / th > < th > key < / th > < th > key_len < / th > < th > ref < / th > < th > rows < / th > < th > Extra < / th > < / tr > < / thead > < tbody > < tr > < td > 1 < / td > < td > SIMPLE < / td > < td > tra_aging_distributions < / td > < td > range < / td > < td > idx_date_corp_process < / td > < td > idx_date_corp_process < / td > < td > 587 < / td > < td > NULL < / td > < td > 605 < / td > < td > Using index condition; Using where < / td > < / tr > < / tbody > < / table >

1 row in set (0.00 sec)

select (1852870+605)/10540764;

< table > < thead > < tr > < th > (1852870,605) / 10540764 < / th > < / tr > < / thead > < tbody > < tr > < td > 0.1758 < / td > < / tr > < / tbody > < / table >

1 row in set (0.00 sec)

Mysql

Oct.17,2021