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.