Example of MySQL use index () optimization query

Use INDEX
After the table name in your query statement, add use index to provide the index column you want MYSQ to refer to
Table, you can let MySQL no longer consider other available indexes.
Eg:select * FROM mytable use INDEX (mod_time, name) ...
?
IGNORE INDEX
If you simply want MySQL to ignore one or more indexes, you can use IGNORE index as the
For Hint.
Eg:select * from Mytale IGNORE INDEX (priority) ...
?
Force INDEX
To force MySQL to use a specific index, you can use the forces index as Hint in the query.
Eg:select * FROM MyTable Force INDEX (mod_time) ...

Force index is typically used to enforce one or more indexes on a query. MySQL usually chooses the correct index based on statistics, but this hint is useful when the query optimizer chooses the wrong index or does not use the index at all.

The IGNORE index hint prevents the query optimizer from using the specified index. When a query with multiple indexes can be used to specify the index that is not required by the optimizer, you can also disallow the use of the index in the query before removing unnecessary indexes.

Force Index/ignore The syntax of INDEX:

SELECT * * * from TABLE [{use| ignore| Force} INDEX (key_list)] WHERE ...

The following example is a change in the execution plan after using IGNORE index, the default execution plan is to scan by the primary key index, and if we use IGNORE Index to ignore the primary key index, it will be performed as a full table scan:

Mysql> DESC SELECT COUNT (*) from test3 where id = 1 \g

1. Row ***************************

Id:1

Select_type:simple

Table:test3

Type:const

Possible_keys:primary

Key:primary

Key_len:4

Ref:const

Rows:1

Extra:using Index

1 row in Set (0.00 sec)

Mysql> DESC SELECT COUNT (*) from TEST3 Ignore index (primary) where id = 1 \g

1. Row ***************************

Id:1

Select_type:simple

Table:test3

Type:all

Possible_keys:null

Key:null

Key_len:null

Ref:null

rows:862560

Extra:using where

1 row in Set (0.00 sec)

Using use index to optimize SQL queries

Let's take a look at the table structure of Arena_match_index, and notice the index structure of the table.
CREATE TABLE ' Arena_match_index ' (
' Tid ' int (ten) unsigned not NULL DEFAULT ' 0 ',
' Mid ' int (ten) unsigned not NULL DEFAULT ' 0 ',
' Group ' int (ten) unsigned not NULL DEFAULT ' 0 ',
' Round ' tinyint (3) unsigned not NULL DEFAULT ' 0 ',
' Day ' date is not NULL DEFAULT ' 0000-00-00 ',
' BeginTime ' datetime not NULL DEFAULT ' 0000-00-00 00:00:00 ',
UNIQUE KEY ' TM ' (' Tid ', ' mid '),
KEY ' mid ' (' mid '),
 KEY ' begintime ' (' BeginTime '),
 KEY ' DG ' (' Day ', ' group '),
KEY ' TD ' (' TID ', ' Day ')
) Engine=myisam DEFAULT Charset=utf8

Then look at the following sql:
SELECT round from Arena_match_index WHERE ' Day ' = ' 2010-12-31 ' and ' group ' = ' begintime ' < ' 20 10-12-31 12:14:28 'ORDER by begintime LIMIT 1;  
This SQL query condition shows that the index that may be used is ' begintime ' and ' DG ', but since the use of order by BeginTime sort MySQL finally chooses to use the ' begintime ' index, the result of explain is:

mysql> explain SELECT round from Arena_match_index WHERE ' Day ' = ' 2010-12-31 ' and ' group ' = ' begintime ' &L T ' 2010-12-31 12:14:28 ' ORDER by begintime LIMIT 1;
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+--------- ----+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+--------- ----+
| 1 | Simple | Arena_match_index | Range | BEGINTIME,DG | begintime | 8 | NULL | 226480 | Using where | 
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+--------- ----+
The results of explain show that using the ' begintime ' Index to scan 22w records, such query performance is very bad, the actual execution is also the first execution (not yet cached data) when it takes more than 30 seconds.

In fact, this query uses a ' DG ' Federated Index for better performance, since the same team also has dozens of games on the same day, so it should be prioritized to use the ' DG ' index to locate the matching data set and then to sortSo how do you tell MySQL to use the specified index? Using the Use index statement：
Mysql> explain SELECT round from Arena_match_index Use Index (DG) WHERE ' Day ' = ' 2010-12-31 ' and ' group ' = + ' begintime ' < ' 2010-12-31 12:14:28 ' ORDER by begintime LIMIT 1;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+---------- -------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+---------- -------------------+
| 1 | Simple | Arena_match_index | Ref | DG | DG| 7 | Const,const | 757 | Using where; Using Filesort | 
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+---------- -------------------+
Explain results show that using the ' DG ' Federated index only needs to scan 757 data, the performance has been directly increased a hundredfold, the actual implementation is almost immediately returned the query results.

In the initial query statement, as long as the order by BeginTime is removed, MySQL will use the ' DG ' Index,once again, the index selection policy that affects MySQL is confirmed by the order by.！
Mysql> explain SELECT round from Arena_match_index WHERE ' Day ' = ' 2010-12-31 ' and ' group ' = ' begintime ' < ' 2010-12-31 12:14:28 ' LIMIT 1;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+---------- ---+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+---------- ---+
| 1 | Simple | Arena_match_index | Ref | BEGINTIME,DG | DG | 7 |  Const,const | 717 | Using where | 
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+---------- ---+

in the example above, MySQL is sometimes not smart, not always the best choice, or it needs to be "tuned" by our developers!

Example of MySQL use index () optimization query