Presto: Do not copy constraints from the target table when creating a temporary table in Mysql before inserting

[ssquan]

Temporary tables without constraints can be inserted faster, and there is no need to copy constraints such as the primary key from the target table.

Related thread in Slack: https://prestosql.slack.com/archives/CFLB9AMBN/p1598339154087800

Use CREATE TABLE AS SELECT WHERE 0=1 instead of CREATE TABLE LIKE to copy a table in MySQL.

[findepi]

Copying primary constraint is hurts performance.
Copying constraints like NOT NULL or CHECK (assuming we copy them) helps fail bogus insert faster.

[ssquan]

CREATE TABLE AS SELECT copies NOT NULL constraint, but not CHECK constraint.

Test on Mysql 8.0.21

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` bigint NOT NULL AUTO_INCREMENT,
  `c2` bigint NOT NULL,
  `c3` varchar(20) NOT NULL,
  PRIMARY KEY (`c1`),
  CONSTRAINT `t1_chk_1` CHECK ((`c2` > 100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> create table t2 as select c1, c2, c3 from t1;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `c1` bigint NOT NULL DEFAULT '0',
  `c2` bigint NOT NULL,
  `c3` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[findepi]

OK. And NOT NULL constraints is something we can handle on Presto side.

@djsstarburst do we do this already? if not, is there an issue covering JDBC connectors?

[djsstarburst]

@djsstarburst do we do this already? if not, is there an issue covering JDBC connectors?

I'm not completely sure of the question, @findepi. As you know, we enforce NOT NULL column constraints in the Presto engine at insert and update time, and that applies to JDBC. I don't know how the other kinds of constraints are handled in the JDBC connector.

[findepi]

and that applies to JDBC.

this i wasn't sure. Thanks for confirming @djsstarburst

other contraints -- of course we do not validate them
but since there are not copied into temp table, MySQL does not validate them either until we try to finish (copy from temp table into actual)

-- so there won't be any negative change in behavior when @ssquan does what is planned.