Table JOIN and CREATE new table extremely slow

Question

I'm using something like this to create a new table:

CREATE TABLE result AS 
(SELECT calls.*, 
      targ_with_planned_calls.*
 FROM calls
      INNER JOIN planned
              ON calls.first_id = planned.another_id);

The two tables have around 60k and 80k rows. One table has 5 columns and the other around 15. I'm using AWS RDS db.t2.medium instance. I've let this query run for as long as 10 minutes and it's still going.

Should this be taking so long? I'm wondering what the performance issue is. I would have thought it would be fairly quick.

Answer 1

You are doing DDL along with the SELECT

Doing CREATE TABLE AS SELECT mechanically does two commands

• CREATE TABLE
• INSERT INTO ... SELECT

This will produce locks on both calls and planned

I have written about this behavior in some of my older posts

• Mar 23, 2012 : MySQL Locks while CREATE TABLE AS SELECT
• Aug 08, 2014 : MySQL consistent nonlocking reads vs. INSERT ... SELECT

Answer 2

Sounds like you have neither index:

On calls an INDEX or PRIMARY KEY starting with first_id

Ditto for planned.another_id

You need one or the other to keep from doing 60k times 80k operations. With an index, it will be only 60k plus 80k operations.

Please provide SHOW CREATE TABLE to confirm, and to let us check for other issues such as dissimilar datatypes.