SQL HINT (SQL HINT) is an important means to optimize the database, which is to add some hints in sql to optimize the effect.
Let's learn about use index, ignore index, and force index;
1. Add use index after the query statement. You can suggest indexes to MySQL for reference, so that MySQL does not consider other indexes.
explain select count(*) from vipshop_finance_account ;
explain select count(*) from vipshop_finance_account use INDEX(idx_user_id) ;
explain select count(*) from vipshop_finance_account ;
SIMPLE vipshop_finance_account index idx_open_wpb_status 2 12816 Using index
explain select count(*) from vipshop_finance_account use INDEX(idx_user_id) ;
SIMPLE vipshop_finance_account index idx_user_id 152 12816 Using index
You can see that sql accepted our suggestion and used our suggested index.
2 if you want mysql to ignore one or more indexes, you can use ignore index,
explain select count(*) from vipshop_finance_account IGNORE INDEX( idx_open_wpb_status);
SIMPLE vipshop_finance_account index idx_withhold_wpb_status 2 12816 Using index
As you can see, at this time mysql accepted our suggestion again, ignoring the index idx ﹣ open ﹣ WPB ﹣ status, and using the index idx ﹣ with old ﹣ WPB ﹣ status.
3 force index
EXPLAIN SELECT * from vipshop_trade_log where related_tradeId>'004201509151046563846447';
EXPLAIN SELECT * from vipshop_trade_log USE INDEX (idx_related_tradeid) where related_tradeId>'004201509151046563846447';
EXPLAIN EXTENDED SELECT * from vipshop_trade_log FORCE INDEX (idx_related_tradeid) where related_tradeId>'004201509151046563846447';
results of enforcement
SIMPLE vipshop_trade_log ALL idx_related_tradeid 2068592 Using where
SIMPLE vipshop_trade_log ALL idx_related_tradeid 2068592 Using where
SIMPLE vipshop_trade_log range idx_related_tradeid idx_related_tradeid 767 1034296 100 Using where
It can be seen that the third sql here, our proposal has been adopted, using idx ﹐ related ﹐ tradeid as the query index, reducing the number of rows searched. The second sql statement does not use indexes, which means that our suggestions are not always accepted by Mysql.