Geek Answers Handbook

Help me optimize this MySQL SELECT statement

I have a query that takes about four minutes to work on a high-performance SSD server without any other notable processes. I would like to do it faster if possible.

A match history is stored in the database for a popular video game called Dota 2. In this game, ten players (five for each team) select a “hero” and fight with it.

The purpose of my request is to create a list of past matches along with how much of the “XP dependency” each team had based on the use of heroes. With 200,000 matches (and a table of relationships between comparisons with heroes of 2,000,000 lines each), the query takes about four minutes. With 1,000,000 matches, approximately 15 are required.

I have full control over the server, so any configuration suggestions are also welcome. Thanks for the help. Here are the details ...

CREATE TABLE matches (
*   match_id BIGINT UNSIGNED NOT NULL,
    start_time INT UNSIGNED NOT NULL,
    skill_level TINYINT NOT NULL DEFAULT -1,
*   winning_team TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (match_id),
    KEY start_time (start_time),
    KEY skill_level (skill_level),
    KEY winning_team (winning_team));

CREATE TABLE heroes (
*   hero_id SMALLINT UNSIGNED NOT NULL,
    name CHAR(40) NOT NULL DEFAULT '',
    faction TINYINT NOT NULL DEFAULT -1,
    primary_attribute TINYINT NOT NULL DEFAULT -1,
    group_index TINYINT NOT NULL DEFAULT -1,
    match_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    win_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
*   xp_from_wins BIGINT UNSIGNED NOT NULL DEFAULT 0,
*   team_xp_from_wins BIGINT UNSIGNED NOT NULL DEFAULT 0,
    xp_from_losses BIGINT UNSIGNED NOT NULL DEFAULT 0,
    team_xp_from_losses BIGINT UNSIGNED NOT NULL DEFAULT 0,
    gold_from_wins BIGINT UNSIGNED NOT NULL DEFAULT 0,
    team_gold_from_wins BIGINT UNSIGNED NOT NULL DEFAULT 0,
    gold_from_losses BIGINT UNSIGNED NOT NULL DEFAULT 0,
    team_gold_from_losses BIGINT UNSIGNED NOT NULL DEFAULT 0,
    included TINYINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (hero_id));

CREATE TABLE matches_heroes (
*   match_id BIGINT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
*   hero_id SMALLINT UNSIGNED NOT NULL,
    xp_per_min SMALLINT UNSIGNED NOT NULL,
    gold_per_min SMALLINT UNSIGNED NOT NULL,
    position TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (match_id, hero_id),
    KEY match_id (match_id),
    KEY player_id (player_id),
    KEY hero_id (hero_id),
    KEY xp_per_min (xp_per_min),
    KEY gold_per_min (gold_per_min),
    KEY position (position));

Query

SELECT
    matches.match_id,
    SUM(CASE     
        WHEN position < 5 THEN xp_from_wins / team_xp_from_wins     
        ELSE 0    
    END) AS radiant_xp_dependence,
    SUM(CASE     
        WHEN position >= 5 THEN xp_from_wins / team_xp_from_wins     
        ELSE 0    
    END) AS dire_xp_dependence,
    winning_team   
FROM
    matches   
INNER JOIN
    matches_heroes     
        ON matches.match_id = matches_heroes.match_id   
INNER JOIN
    heroes     
        ON matches_heroes.hero_id = heroes.hero_id   
GROUP BY
    matches.match_id

Results Examples

match_id   | radiant_xp_dependence | dire_xp_dependence | winning_team

2298874871 | 1.0164                | 0.9689             | 1
2298884079 | 0.9932                | 1.0390             | 0
2298885606 | 0.9877                | 1.0015             | 1

EXPLAIN

id | select_type | table          | type   | possible_keys            | key     | key_len | ref                            | rows | Extra

1  | SIMPLE      | heroes         | ALL    | PRIMARY                  | NULL    | NULL    | NULL                           | 111  | Using temporary; Using filesort
1  | SIMPLE      | matches_heroes | ref    | PRIMARY,match_id,hero_id | hero_id | 2       | dota_2.heroes.hero_id          | 3213 |
1  | SIMPLE      | matches        | eq_ref | PRIMARY                  | PRIMARY | 8       | dota_2.matches_heroes.match_id | 1    |

Machine specifications

  • Intel Xeon E5
  • E5-1630v3 4 / 8t
  • 3.7 / 3.8 GHz
  • 64 GB RAM
  • DDR4 ECC 2133 MHz
  • 2 x 480GB SSD SOFT

Database

  • MariaDB 10.0
  • Innodb
+4
source share
4 answers

In all likelihood, the main driver of performance is GROUP BY. Sometimes in MySQL it may be faster to use correlated subqueries. So, try writing the query as follows:

SELECT m.match_id,
       (SELECT SUM(h.xp_from_wins / h.team_xp_from_wins)
        FROM matches_heroes mh INNER JOIN
             heroes h   
             ON mh.hero_id = h.hero_id
        WHERE m.match_id = mh.match_id AND mh.position < 5
       ) AS radiant_xp_dependence,
       (SELECT SUM(h.xp_from_wins / h.team_xp_from_wins)
        FROM matches_heroes mh INNER JOIN
             heroes h   
             ON mh.hero_id = h.hero_id
        WHERE m.match_id = mh.match_id AND mh.position >= 5
       ) AS dire_xp_dependence,
       m.winning_team   
FROM matches m;

Then you want indexes on:

  • matches_heroes(match_id, position)
  • heroes(hero_id, xp_from_wins, team_xp_from_wins)

For completeness, you may also need this index:

  • matches(match_id, winning_team)

This would be more important if you added order by match_idto the request.

+3

; , . .

, , . , , , .

CREATE INDEX matches_quick ON matches(match_id, winning_team);

CREATE INDEX heroes_quick ON heroes(hero_id, xp_from_wins, team_xp_from_wins);

CREATE INDEX matches_heroes_quick ON matches_heroes (match_id, hero_id, position);

, , , , . , , , , . .

+2

?: -)

, . , - , ?

, , , , , , .

archived_results. archived matches. archived_results TRUE. , archived_results , :

select match_id, radiant_xp_dependence, radiant_xp_dependence winning_team
from archived_results
union all
SELECT
    matches.match_id,
    SUM(CASE     
        WHEN position < 5 THEN xp_from_wins / team_xp_from_wins     
        ELSE 0    
    END) AS radiant_xp_dependence,
...
WHERE matches.archived = FALSE
GROUP BY matches.match_id;
+1

. " MySQL" , InnoDB. , , 8 , innodb_buffer_pool_size my.cnf. 1308 114.

my.cnf ( , ).

[mysqld]
bind-address=127.0.0.1
character-set-server=utf8
collation-server=utf8_general_ci
innodb_buffer_pool_size=8G
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=8M
innodb_log_file_size=64M
innodb_read_io_threads=64
innodb_write_io_threads=64

, , . -.

+1

More articles:


All Articles