Use useindex to optimize SQL queries _ MySQL

Source: Internet
Author: User
Use useindex to optimize SQL queries

Let's take a look at the table structure of arena_match_index. pay attention to the index structure of the table.

Create table 'Arena _ match_index '(
'Tid' int (10) unsigned not null default '0 ',
'Mid 'Int (10) unsigned not null default '0 ',
'Group' int (10) unsigned not null default '0 ',
'Round 'tinyint (3) unsigned not null default '0 ',
'Day' date not null default '2017-00-00 ',
'Begintime' datetime not null default '2017-00-00 00:00:00 ',
Unique key 'TT' ('tid', 'mid '),
KEY 'mid '('mid '),
KEY 'begintime' ('begintime '),
KEY 'dg '('day', 'Group '),
KEY 'TD '('tid', 'day ')
) ENGINE = MyISAM default charset = utf8

Next, let's look at the following SQL:

SELECT round FROM arena_match_index WHERE 'day' = '2017-12-31 'AND 'group' = 18 AND 'begintime' <'2017-12-31 12:14:28' order by begintime LIMIT 1;

The query conditions of this SQL statement show that the indexes that may be used include 'begintime' and 'dg '. However, because order by begintime is used to sort mysql, the 'begintime' index is selected, the explain result is:

Mysql> explain SELECT round FROM arena_match_index WHERE 'day' = '2017-12-31 'AND 'group' = 18 AND 'begintime' <'2017-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 explain results show that 22 million records need to be scanned using the 'begintime' index. the query performance is very poor, and the actual execution is also the first time (when no data is cached) it takes more than 30 seconds.

In fact, this query uses the 'dg 'Union index to provide better performance, because dozens of matches are played in the same group on the same day, therefore, the 'dg 'index should be used first to locate matched data sets and then sort them. how can we tell mysql to use the specified index? Use the use index statement:

Mysql> explain SELECT round FROM arena_match_index use index (dg) WHERE 'day' = '2017-12-31 'AND 'group' = 18 AND 'begintime' <'2017-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 | 7 | const, const | 757 | Using where; Using filesort |
+ ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------------- + ------ + --------------------------- +

The explain results show that you only need to scan 757 pieces of data using the 'dg 'joint index, and the performance has been improved by hundreds of times. The actual execution result is almost immediately returned.

In the initial query statement, if order by begintime is removed, mysql will use the 'dg 'index. once again, this confirms that order by will affect the index selection policy of mysql!

Mysql> explain SELECT round FROM arena_match_index WHERE 'day' = '2017-12-31 'AND 'group' = 18 AND 'begintime' <'2017-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 | 7 | const, const | 717 | Using where |
+ ---- + ------------- + ------------------- + ------ + --------------- + ------ + --------- + ------------- + ------ + ------------- +

Through the above example, mysql is sometimes not smart, and it is not always the best choice. we still need developers to "tune up" it "!

BitsCN.com
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.