Question 286621 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Optimise query with 2x LEFT JOIN from same table

I have the following query:

SELECT 
    match_main.checked,
    match_main.match_main_id,
    match_main.updated,
    match_main.created
FROM
    match_main
        LEFT JOIN
    match_team AS mt1 ON mt1.match_main_id = match_main.match_main_id
        AND mt1.team_number = 1
        AND mt1.version_number = 0
        LEFT JOIN
    match_team AS mt2 ON mt2.match_main_id = match_main.match_main_id
        AND mt2.team_number = 2
        AND mt2.version_number = 0
WHERE
    mt1.team_id = 557949
        OR mt2.team_id = 557949

Where match_main is a table of sports matches and match_team is a table of the teams that played in a given sports match.

match_main contains around 5m records and match_team contains around 10m records.

The query above takes over 5 mins to return about 1700 records and I can’t figure out how to optimise it further.

This is the EXPLAIN query output:

+----+-------------+------------+------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------+---------+----------+-------------+
| id | select_type |   table    | partitions |  type  |                                                    possible_keys                                                    |                   key                    | key_len |                   ref                    |  rows   | filtered |    Extra    |
+----+-------------+------------+------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------+---------+----------+-------------+
|  1 | SIMPLE      | match_main | NULL       | ALL    | NULL                                                                                                                | NULL                                     | NULL    | NULL                                     | 4756279 |      100 | NULL        |
|  1 | SIMPLE      | mt1        | NULL       | eq_ref | uq__match_team__match__team_id__version,uq__match_team__match__team_num__version,ix__match_team__match_main_id,comp | uq__match_team__match__team_num__version | 6       | itf.match_main.match_main_id,const,const |       1 |      100 | NULL        |
|  1 | SIMPLE      | mt2        | NULL       | eq_ref | uq__match_team__match__team_id__version,uq__match_team__match__team_num__version,ix__match_team__match_main_id,comp | uq__match_team__match__team_num__version | 6       | itf.match_main.match_main_id,const,const |       1 |      100 | Using where |
+----+-------------+------------+------------+--------+---------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+------------------------------------------+---------+----------+-------------+

Here are the SHOW CREATE TABLE outputs:

CREATE TABLE `match_main` (
  `match_main_id` int NOT NULL AUTO_INCREMENT,
  `checked` datetime NOT NULL,
  `updated` timestamp NOT NULL,
  `created` timestamp NOT NULL,
  PRIMARY KEY (`match_main_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2121471809 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `match_team` (
  `match_team_id` int NOT NULL AUTO_INCREMENT,
  `match_main_id` int NOT NULL,
  `team_id` int NOT NULL,
  `team_number` tinyint NOT NULL,
  `version_number` tinyint NOT NULL,
  `updated` timestamp NOT NULL,
  `created` timestamp NOT NULL,
  PRIMARY KEY (`match_team_id`),
  UNIQUE KEY `uq__match_team__match__team_id__version` (`match_main_id`,`team_id`,`version_number`),
  UNIQUE KEY `uq__match_team__match__team_num__version` (`match_main_id`,`team_number`,`version_number`),
  KEY `ix__match_team__team_id` (`team_id`),
  KEY `ix__match_team__match_main_id` (`match_main_id`),
  CONSTRAINT `fk__match_team__match_main_id` FOREIGN KEY (`match_main_id`) REFERENCES `match_main` (`match_main_id`),
  CONSTRAINT `fk__match_team__team_id` FOREIGN KEY (`team_id`) REFERENCES `team` (`team_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9297542 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I need to retain the two aliased match_team tables as when I expand this query I’ll need to link these tables to the team tables. This is so I can return an output of a match on a single row with columns for details on all the individual teams that played in a match. I’ve excluded this to keep the MRE simple.

Tags:

2

Answers


  1. 0

    Redefine the column order in one of the unique keys:

    ALTER TABLE match_team
  DROP KEY `uq__match_team__match__team_num__version`,
  ADD UNIQUE KEY `uq__match_team__team_num__version__match` (`team_number`,`version_number`,`match_main_id`);

    Then try this query:

    SELECT m.checked, m.match_main_id, m.updated, m.created
FROM match_team AS mt1
INNER JOIN match_team AS mt2
  ON mt1.match_main_id = mt2.match_main_id
INNER JOIN match_main AS m
  ON m.match_main_id = mt1.match_main_id
WHERE 557949 IN (mt1.team_id, mt2.team_id)
  AND mt1.team_number = 1
  AND mt1.version_number = 0
  AND mt2.team_number = 2
  AND mt2.version_number = 0;

    It should use the new uq__match_team__team_num__version__match index for both mt1 and mt2, then join to match_main by its primary key. This eliminates the table-scan of 4 million rows on match_main.

    Here’s the EXPLAIN report I got when I tested:

    *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt1
   partitions: NULL
         type: ref
possible_keys: uq__match_team__match__team_id__version,uq__match_team__team_num__version__match,ix__match_team__match_main_id
          key: uq__match_team__team_num__version__match
      key_len: 2
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: mt2
   partitions: NULL
         type: eq_ref
possible_keys: uq__match_team__match__team_id__version,uq__match_team__team_num__version__match,ix__match_team__match_main_id
          key: uq__match_team__team_num__version__match
      key_len: 6
          ref: const,const,test.mt1.match_main_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.mt1.match_main_id
         rows: 1
     filtered: 100.00
        Extra: NULL
    Login or Signup to reply.
  2. 0

    What is the purpose of the second JOIN? You could try with just one:

    SELECT 
    mm.checked,
    mm.match_main_id,
    mm.updated,
    mm.created
FROM match_team AS mt 
   JOIN match_main AS mm ON mm.match_main_id = mt.match_main_id 
WHERE mt.team_id = 557949 AND mt.version_number = 0 
  AND mt.team_number in (1,2);

    Also, the LEFT JOIN becomes a normal JOIN when you move the condition to the WHERE-clause.

    See a dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search