Stacks of overflow Stacks of overflow - 1 month ago 7
MySQL Question

Is MySQL Query framed properly?

I'm attempting to run the following Query on an AWS ec2 xlarge instance:

mysql> CREATE TABLE 3 SELECT * FROM TABLE 2 WHERE ID IN (SELECT ID FROM TABLE1);


I attempted using ec2 as I thought perhaps it was my laptop making the query taking long as Table1 has 14000 rows and 6 columns and table 2 has around 1 million rows and 11 columns. However I get:

Write failed: Broken pipe

When using the aws instance, and DBMS timeout on my laptop.

Should the Query be taking > than these timeout triggers? Anyway to word the query better?

Answer

MySQL is very poor at executing WHERE table1.id IN (SELECT table2.id ...). It's better to use a JOIN.

CREATE TABLE Table3
SELECT t2.*
FROM Table2 AS t2
JOIN Table1 AS t1 ON t1.id = t2.id