Why is LEFT JOIN slower than INNER JOIN?

Question

I have two queries, the first (inner join) is super fast, and the second (left join) is very slow. How to quickly execute the second request?

EXPLAIN SELECT saved.email FROM saved INNER JOIN finished ON finished.email = saved.email;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  finished    index   NULL    email   258 NULL    32168   Using index
1   SIMPLE  saved   ref email   email   383 func    1   Using where; Using index

EXPLAIN SELECT saved.email FROM saved LEFT JOIN finished ON finished.email = saved.email;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  saved   index   NULL    email   383 NULL    40971   Using index
1   SIMPLE  finishedindex   NULL    email   258 NULL    32168   Using index

Edit: I added table information for both tables below.

CREATE TABLE `saved` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) DEFAULT NULL,
  `email` varchar(127) NOT NULL,
  [omitted fields include varchar, text, longtext, int],
  PRIMARY KEY (`id`),
  KEY `slug` (`slug`),
  KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=56329 DEFAULT CHARSET=utf8;

CREATE TABLE `finished` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) DEFAULT NULL,
  `submitted` int(11) DEFAULT NULL,
  `status` int(1) DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  [omitted fields include varchar, text, longtext, int],
  PRIMARY KEY (`id`),
  KEY `assigned_user_id` (`assigned_user_id`),
  KEY `event_id` (`event_id`),
  KEY `slug` (`slug`),
  KEY `email` (`email`),
  KEY `city_id` (`city_id`),
  KEY `status` (`status`),
  KEY `recommend` (`recommend`),
  KEY `pending_user_id` (`pending_user_id`),
  KEY `submitted` (`submitted`)
) ENGINE=MyISAM AUTO_INCREMENT=33063 DEFAULT CHARSET=latin1;

Answer 1

With INNER JOIN, MySQL usually starts with the table with the fewest rows. In this case, it starts from the table finishedand looks for the corresponding entry in saved, using the index on saved.email.

LEFT JOIN ( ) MySQL ( ). MySQL saved, finished. finished.email , .

Edit

, , , MySQL (finished.email) utf8 latin1. , . , MySQL .

MySQL () latin1, , utf8, unicode_ci ( saved.email latin1 collation utf8), ( finished.email, utf8 latin1).

, , , , .

Answer 2

LEFT JOIN , INNER JOIN, .

EXPLAIN, , MySQL . ( , , , MySQL 5.5 .)

INNER JOIN MySQL "ref" ( ) .

LEFT JOIN , MySQL , . , MySQL . , , .

EXPLAIN, (40971 * 32168 =) 1,317,955,128 .

INNER JOIN , . ( , .

-- LEFT JOIN
id select table    type   key   key_len ref    rows  Extra
-- ------ -------- -----  ----- ------- ----  -----  ------------------------
1  SIMPLE saved    index  email     383 NULL  40971  Using index
1  SIMPLE finished index  email     258 NULL  32168  Using index

-- INNER JOIN 
id select table    type   key   key_len ref    rows  Extra
-- ------ -------- -----  ----- ------- ----  -----  ------------------------  
1  SIMPLE finished index  email     258 NULL  32168  Using index
1  SIMPLE saved    ref    email     383 func      1  Using where; Using index
                   ^^^^^                ^^^^  ^^^^^  ^^^^^^^^^^^^

---

. email CREATE TABLE, .

, , MySQL .

---

Q2: LEFT JOIN ?

A: , , , , , , .

, " " finished , "" , . , . :

SELECT saved.email FROM saved

Answer 3

, , , .

inner joins , ( , ). , .

left join , - .

. , , select * - , .

Answer 4

Data types saved.emailand finished.emaildiffer in two respects. Firstly, they have different lengths. Secondly, it finished.emailcan be NULL. Therefore, your operation LEFT JOINcannot use the index on finished.email.

Can you change the definition finished.emailto this so that it matches the field with which you connect it?

`email` varchar(127) NOT NULL

If you do, you are likely to get acceleration.