MySQL: why is left join slower than inner join? Optimization help needed

Question

I have a MySQL query that joins between two tables. I need to match the call id from the first table with the second table. The second table may not have a call identifier, so I will need to join the tables. The following is a request, it takes about 125 seconds to complete.

select uniqueid, TRANTAB.DISP, TRANTAB.DIAL FROM closer_log LEFT JOIN (select call_uniqueId, sum(dispo_duration) as DISP, sum(dialing_duration) as DIAL from agent_transition_log group by call_uniqueId) TRANTAB on closer_log.uniqueid=TRANTAB.call_uniqueId; 

Here is an explanation of the query output with a left join.

 +----+-------------+----------------------+-------+---------------+----------------------------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+-------+---------------+----------------------------+---------+------+--------+-------------+ | 1 | PRIMARY | closer_log | index | NULL | uniqueid | 43 | NULL | 37409 | Using index | | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 32535 | | | 2 | DERIVED | agent_transition_log | index | NULL | index_agent_transition_log | 43 | NULL | 159406 | | +----+-------------+----------------------+-------+---------------+----------------------------+---------+------+--------+-------------+ 

If I make an inner join, the runtime is about 2 seconds.

 select uniqueid, TRANTAB.DISP, TRANTAB.DIAL FROM closer_log JOIN (select call_uniqueId, sum(dispo_duration) as DISP, sum(dialing_duration) as DIAL from agent_transition_log group by call_uniqueId) TRANTAB on closer_log.uniqueid=TRANTAB.call_uniqueId; 

Explain the query output with an inner join.

  +----+-------------+----------------------+-------+------------------------------------+----------------------------+---------+-----------------------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+-------+------------------------------------+----------------------------+---------+-----------------------+--------+--------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 32535 | | | 1 | PRIMARY | closer_log | ref | uniqueid,index_closer_log | index_closer_log | 43 | TRANTAB.call_uniqueId | 1 | Using where; Using index | | 2 | DERIVED | agent_transition_log | index | NULL | index_agent_transition_log | 43 | NULL | 159406 | | +----+-------------+----------------------+-------+------------------------------------+----------------------------+---------+-----------------------+--------+--------------------------+ 

My question is: why is the inner join much faster than the left join. Does my request have a logical error that causes slow execution? What are my optimization options. Call IDs in both tables are indexed.

Edit 1) Added table descriptions

 mysql> desc agent_transition_log; +--------------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+----------------------+------+-----+---------+-------+ | user_log_id | int(9) unsigned | NO | MUL | NULL | | | event_time | datetime | YES | | NULL | | | dispoStatus | varchar(6) | YES | | NULL | | | call_uniqueId | varchar(40) | YES | MUL | NULL | | | xfer_call_uid | varchar(40) | YES | | NULL | | | pause_duration | smallint(5) unsigned | YES | | 0 | | | wait_duration | smallint(5) unsigned | YES | | 0 | | | dialing_duration | smallint(5) unsigned | YES | | 0 | | | ring_wait_duration | smallint(5) unsigned | YES | | 0 | | | talk_duration | smallint(5) unsigned | YES | | 0 | | | dispo_duration | smallint(5) unsigned | YES | | 0 | | | park_duration | smallint(5) unsigned | YES | | 0 | | | rec_duration | smallint(5) unsigned | YES | | 0 | | | xfer_wait_duration | smallint(5) unsigned | YES | | 0 | | | logged_in_duration | smallint(5) unsigned | YES | | 0 | | | sub_status | varchar(6) | YES | | NULL | | +--------------------+----------------------+------+-----+---------+-------+ 16 rows in set (0.00 sec) mysql> desc closer_log; +----------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------------------+------+-----+---------+----------------+ | closecallid | int(9) unsigned | NO | PRI | NULL | auto_increment | | lead_id | int(9) unsigned | NO | MUL | NULL | | | list_id | bigint(14) unsigned | YES | | NULL | | | campaign_id | varchar(20) | YES | MUL | NULL | | | call_date | datetime | YES | MUL | NULL | | | start_epoch | int(10) unsigned | YES | | NULL | | | end_epoch | int(10) unsigned | YES | | NULL | | | length_in_sec | int(10) | YES | | NULL | | | status | varchar(6) | YES | | NULL | | | phone_code | varchar(10) | YES | | NULL | | | phone_number | varchar(18) | YES | MUL | NULL | | | user | varchar(20) | YES | | NULL | | | comments | varchar(255) | YES | | NULL | | | processed | enum('Y','N') | YES | | NULL | | | queue_seconds | decimal(7,2) | YES | | 0.00 | | | user_group | varchar(20) | YES | | NULL | | | xfercallid | int(9) unsigned | YES | | NULL | | | uniqueid | varchar(40) | YES | MUL | NULL | | | callerid | varchar(40) | YES | | NULL | | | agent_only | varchar(20) | YES | | | | | queue_position | smallint(4) unsigned | YES | | 1 | | | root_uid | varchar(40) | YES | | NULL | | | parent_uid | varchar(40) | YES | | NULL | | | extension | varchar(100) | YES | | NULL | | | alt_dial | varchar(6) | YES | | NULL | | | talk_duration | smallint(5) unsigned | YES | | 0 | | | did_pattern | varchar(50) | YES | | NULL | | +----------------+----------------------+------+-----+---------+----------------+ 

Answer 1

The left join is looking for fields on the left + unsurpassed records on the right, so you need to check each joined field in the right table, which can be NULL (if you do not have an index in the fields for this JOIN, this means that the query will check the entire right table every time). The inner join looks only for direct matches, so you may not have to iterate over the entire table to make the join (especially if you are joining indexed fields).

By the way, if you want to display the entries mentioned in agent_transition_log, you do not need to join at all:

 select call_uniqueId, sum(dispo_duration) as DISP, sum(dialing_duration) as DIAL from agent_transition_log group by call_uniqueId; 

will do the job.

OR if you want to add the missing entries:

 SELECT call_uniqueId, sum(dispo_duration) as DISP, sum(dialing_duration) as DIAL from agent_transition_log group by call_uniqueId UNION SELECT uniqueid as call_uniqueid, NULL as DISP, NULL as DIAL from closer_log WHERE uniqueid not in (SELECT call_uniqueid FROM agent_transition_log);