Geek asks and answers

Optimize JOIN with LEFT JOIN

I have a problem optimizing this query:

SELECT a.id
FROM a
JOIN b ON a.id=b.id
LEFT JOIN c ON a.id=c.id
WHERE
   (b.c1='12345' OR c.c1='12345')
   AND (a.c2=0 OR b.c3=1)
   AND a.c4='active'
GROUP BY a.id;

The request takes 7 seconds, while it takes 0 s when only one of bor is cconnected. EXPLAIN:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ref
possible_keys: PRIMARY(id),c4,c2
          key: c4
      key_len: 1
          ref: const
         rows: 80775
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: id_c1_unique,id
          key: id_c1
      key_len: 4
          ref: database.a.id
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: id_c1_unique,id,c1,c3
          key: id
      key_len: 4
          ref: database.a.id
         rows: 2
        Extra: Using where

There is always exactly one corresponding line from band no more than one corresponding line from c. This will happen much faster if MySQL starts by retrieving strings band cthat correspond to a literal c1, then append abased on id, but instead, it starts with a.

More details:

  • Myisam
  • All columns have indexes (_unique UNIQUE)
  • All columns NOT NULL

What I tried:

  • Reorder JOINs
  • Moving WHERE clauses to ON clauses.
  • b.c1 c.c1 (WHERE b.id = (SELECT b.id FROM b WHERE c1 = '12345'))
  • b c

, , SELECT UNION, , - , .

: CREATE TABLEs

CREATE TABLE .

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` tinyint(1) NOT NULL,
  `c4` enum('active','pending','closed') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `c2` (`c2`)
  KEY `c4` (`c4`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
    `b_id` int(11) NOT NULL AUTO_INCREMENT,
    `id` int(11) NOT NULL DEFAULT '0',
    `c1` int(11) NOT NULL,
    `c3` tinyint(1) NOT NULL,
    PRIMARY KEY (`b_id`),
    UNIQUE KEY `id_c1_unique` (`id`,`c1`),
    KEY `c1` (`c1`),
    KEY `c3` (`c3`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `c` (
    `c_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `id` int(11) NOT NULL,
    `c1` int(11) NOT NULL,
    PRIMARY KEY (`c_id`),
    UNIQUE KEY `id_c1_unique` (`id`,`c1`),
    KEY `id` (`id`),
    KEY `c1` (`c1`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+3
3

OP .

, , , , MySQL, , LEFT JOIN, . LEFT JOIN RIGHT JOIN Optimization MySQL:

MySQL A LEFT JOIN B join_condition :

  • B ​​ A , A

:

SELECT a.id
FROM a
LEFT JOIN c ON a.id=c.id
GROUP BY a.id;

A -, , c . MySQL c :

SELECT a.id
FROM c
LEFT JOIN a ON c.id=a.id
GROUP BY a.id;

. -, - , , , , LEFT JOIN. , , MySQL ( , LEFT JOIN). , , , ​​, , , .

, . B c , . JOIN LEFT JOIN, .

, , JOIN. .

TL; DR: , ( ). B c . , B c.

0

, , , .

, , , , " " - . . , yo a.id a.c2 a.c4. , .

, . , , "" .

- , ( ).

id c2 c4 b id c1 c3 c id c1

0
select STRAIGHT_JOIN 
      distinct a.ID
   from
      a
         join b
            on a.ID = b.ID
         left join c
            on a.id = c.id
            and c.c1 = '12345'
   where
          a.C4 = 'active'
      and ( a.c2 = 0 or b.c3 = 1 )
      and ( b.c1 = '12345' or c.c1='12345' )
0
source

More articles:


All Articles