First look at the ARENA_MATCH_INDEX table structure, you notice the index structure of the table
Copy Code code as follows:
CREATE TABLE ' Arena_match_index ' (
' Tid ' int (a) unsigned not NULL DEFAULT ' 0 ',
' Mid ' int (a) unsigned not NULL DEFAULT ' 0 ',
' Group ' int 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:
Copy Code code as follows:
SELECT round from Arena_match_index WHERE ' Day ' = ' 2010-12-31 ' and ' group ' = ' and ' BeginTime ' < ' 2010-12-31 12:14:28 ' ORDER by BeginTime LIMIT 1;
This SQL query condition shows that the indexes that may be used are ' begintime ' and ' DG ', but because of the use of order by BeginTime to sort MySQL with the last option to use the ' begintime ' index, explain results are:
Copy Code code as follows:
Mysql> explain SELECT round from Arena_match_index WHERE ' Day ' = ' 2010-12-31 ' and ' group ' = ' and ' 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 | Range | BEGINTIME,DG |<strong> </STRONG>begintime<STRONG> </strong>| 8 | NULL | 226480 | Using where |
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+--------- ----+
Explain's results show that using the ' begintime ' Index to scan 22w records, such query performance is very bad, and the actual performance is also the first time (there is no caching of data) requires more than 30 seconds.
In fact, the query uses the ' DG ' Federated Index for better performance because there are dozens of games in the same group on the same day, so it's a priority to use the ' DG ' index to navigate to a matching set of data and then sort it, so how do you tell MySQL to use the specified index? Using the USE index statement:
Copy Code code as follows:
Mysql> explain SELECT round from Arena_match_index use Index (DG) WHERE ' Day ' = ' 2010-12-31 ' and ' group ' = ' and ' Beg Intime ' < ' 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 | The Using where; Using Filesort |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+---------- -------------------+
Explain results show that using the ' DG ' Federated index only needs to scan 757 data, the performance is directly increased by a hundredfold, and the actual implementation is almost immediately returned to the query results.
In the original query statement as long as the order by BeginTime removed, MySQL will use the ' DG ' Index, again confirmed the order by will affect the MySQL index selection strategy!
Copy Code code as follows:
Mysql> explain SELECT round from Arena_match_index WHERE ' Day ' = ' 2010-12-31 ' and ' group ' = ' and ' 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 |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+---------- ---+
According to the above example, MySQL is sometimes not smart, not always make the best choice, or need our developers to the "tuning"!
