内连接mysql优化

mysql优化"/>

内连接mysql优化

我有一个

MySQL查询,它连接在两个表之间.我需要将第一个表中的调用id映射到第二个表.第二个表可能没有调用id,因此我需要保持连接表.以下是查询,完成大约需要125秒.

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;

这是左连接查询的解释输出.

+----+-------------+----------------------+-------+---------------+----------------------------+---------+------+--------+-------------+

| 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 | | ALL | NULL | NULL | NULL | NULL | 32535 | |

| 2 | DERIVED | agent_transition_log | index | NULL | index_agent_transition_log | 43 | NULL | 159406 | |

+----+-------------+----------------------+-------+---------------+----------------------------+---------+------+--------+-------------+

如果我进行内部连接,则执行时间约为2秒.

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;

用内部联接解释查询的输出.

+----+-------------+----------------------+-------+------------------------------------+----------------------------+---------+-----------------------+--------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------------------+-------+------------------------------------+----------------------------+---------+-----------------------+--------+--------------------------+

| 1 | PRIMARY | | 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 | |

+----+-------------+----------------------+-------+------------------------------------+----------------------------+---------+-----------------------+--------+--------------------------+

我的问题是,为什么内部联接比左联接快得多.我的查询是否有任何导致执行缓慢的逻辑错误？我的优化选项有哪些？两个表中的调用ID都被编入索引.

编辑1)添加了表格说明

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 | |

+----------------+----------------------+------+-----+---------+----------------+