0

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.

  • Try to use specific column names instead of SELECT *. You may use CREATE table LIKE followed by INSERT will be much faster – Koustuv Chatterjee May 12 '15 at 20:27
  • I guess the SELECT ... targ_with_planned_calls.* should be SELECT ... planned.*. Right? – ypercubeᵀᴹ May 13 '15 at 0:12
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

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.