Re: inner join vs left join - huge performance difference

[michael cook]

There are a few extra keys that I didn't describe, but here are the EXPLAIN results:

INNER JOIN:
+----+-------------+-------+------------+-------+-----------------+-------+---------+------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key   | key_len | ref        | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+-----------------+-------+---------+------------+------+----------+------------------------------------+
|  1 | SIMPLE      | table | NULL       | range | date,type,LocID | LocID | 6       | NULL       |  515 |     9.57 | Using index condition; Using where |
|  1 | SIMPLE      | table | NULL       | ref   | date,type,LocID | LocID | 6       | const,func |   19 |     9.57 | Using index condition; Using where |
+----+-------------+-------+------------+-------+-----------------+-------+---------+------------+------+----------+------------------------------------+

LEFT JOIN:
+----+-------------+-------+------------+-------+-----------------+-------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key   | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+-----------------+-------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | table | NULL       | range | date,type,LocID | LocID | 6       | NULL  |  527 |     9.57 | Using index condition;Using where  |
|  1 | SIMPLE      | table | NULL       | ref   | date,type,LocID | type  | 1       | const |   10 |   100.00 | Using where                        |
+----+-------------+-------+------------+-------+-----------------+-------+---------+-------+------+----------+------------------------------------+