MySQL query took extremely long time to finish and it was in 'Sending data' state This query took extremly long time and it never seemed to finish. 

Below is the query:

INSERT INTO `zhihu_answer_meta_reconcile`

    SELECT a.* 
    FROM `zhihu_answer_meta` a 
    LEFT JOIN `zhihu_answer_meta_staging` b 
            ON a.answer_id   = b.answer_id 
           AND a.insert_time = b.insert_time 
    WHERE b.answer_id IS NULL

UNION ALL

    SELECT * 
    FROM `zhihu_answer_meta_staging`



The row numbers of these tables are:

zhihu_answer_meta_staging: 1074588
zhihu_answer_meta: 2315697
zhihu_answer_meta_reconcile: 0  this table is a newly created table for inserting data into.


Besides, both of three tables' structure is the same.

CREATE TABLE `zhihu_answer_meta` (
  `answer_id` bigint(20) NOT NULL,
  `author_name` varchar(500) DEFAULT NULL,
  `reward_member_count` int(11) DEFAULT NULL,
  `answer_created` datetime DEFAULT NULL,
  `voteup_count` int(11) DEFAULT NULL,
  `is_advertiser` tinyint(1) DEFAULT NULL,
  `author_url_token` varchar(500) DEFAULT NULL,
  `comment_count` int(11) DEFAULT NULL,
  `is_org` tinyint(1) DEFAULT NULL,
  `question_type` varchar(100) DEFAULT NULL,
  `question_created` datetime DEFAULT NULL,
  `author_type` varchar(100) DEFAULT NULL,
  `insert_time` datetime DEFAULT NULL,
  `badge_num` int(11) DEFAULT NULL,
  `answer_updated` datetime DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `reward_total_money` int(11) DEFAULT NULL,
  `author_id` varchar(300) DEFAULT NULL,
  `can_comment` tinyint(1) DEFAULT NULL,
  `question_id` bigint(20) DEFAULT NULL,
  UNIQUE KEY `answer_index` (`answer_id`,`insert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 



Appretiate if anyone can give some advice. Thanks a lot.
the combination of answer_id and insert_time is unique. I already created that index for the combination



Create this UNIQUE index on destination table (zhihu_answer_meta_reconcile).
Perform the action you need with 2 separate queries:


-- TRUNCATE `zhihu_answer_meta_reconcile`;
INSERT INTO `zhihu_answer_meta_reconcile`
    SELECT * FROM `zhihu_answer_meta_staging`;
INSERT IGNORE INTO `zhihu_answer_meta_reconcile`
    SELECT * FROM `zhihu_answer_meta`;


These queries may be combined into one line. While using from some code check that the inserting function/method allows multi-query in that case.