SQL – "CREATE TABLE AS SELECT" Statement

In this blog post, I’ll provide an explanation of why you should avoid using the CREATE TABLE AS SELECT statement.

The SQL statement “create table <table_name> as select …” is used to create a normal or temporary table and materialize the result of the select. Some applications use this construct to create a copy of the table. This is one statement that will do all the work, so you do not need to create a table structure or use another statement to copy the structure.

At the same time there are a number of problems with this statement:

1. You don’t create indexes for the new table
2. You are mixing transactional and non-transactional statements in one transaction. As with any DDL, it will commit current and unfinished transactions
3. CREATE TABLE … SELECT is not supported when using GTID-based replication
4. Metadata locks won’t release until the statement is finished

CREATE TABLE AS SELECT statement can break things very badly

Let’s imagine we need to transfer money from one account to another (classic example). But in addition to just transferring funds, we need to calculate fees. The developers decide to create a table to perform a complex calculation.

Then the transaction looks like this:

The “create table as select … join … ” commits a transaction that is not safe. In case of an error, the second account obviously will not be credited by the second account debit that has been already committed!

Well, instead of “create table … “, we can use “create temporary table …” which fixes the issue, as temporary table creation is allowed.

GTID issue

If you try to use CREATE TABLE AS SELECT when GTID is enabled (and ENFORCE_GTID_CONSISTENCY = 1) you get this error:

The application code may break.

Metadata lock issue

Metadata lock issue for CREATE TABLE AS SELECT is less known. (More information about the metadata locking in general). Please note: MySQL metadata lock is different from InnoDB deadlock, row-level locking and table-level locking.

This quick simulation demonstrates metadata lock:

session1:

session2:

— blocked statement

This statement is waiting for the metadata lock:

session3:

The same can happen another way: a slow select query can prevent some DDL operations (i.e., rename, drop, etc.):

As we can see, CREATE TABLE AS SELECT can affect other queries. However, the problem here is not the metadata lock itself (the metadata lock is needed to preserve consistency). The problem is that the metadata lock will not be released until the statement is finished. 

The fix is simple: copy the table structure first by doing “create table new_table like old_table”, then do “insert into new_table select …”. The metadata lock is still held for the create table part (very short) but isn’t for the “insert … select” part (the total time to hold the lock is much shorter). To illustrate the difference, let’s look at two cases:

1. With “create table table_new as select … from table1“, other application connections can’t read from the destination table (table_new) for the duration of the statement (even “show fields from table_new” will be blocked)
2. With “create table new_table like old_table” + “insert into new_table select …”, other application connections can’t read from the destination table during the “insert into new_table select …” part.

In some cases, however, the table structure is not known beforehand. For example, we may need to materialize the result set of a complex select statement, involving joins and/or group by. In this case, we can use this trick:

The first statement creates a table structure and doesn’t insert any rows (LIMIT 0). The first statement places a metadata lock. However, it is very quick. The second statement actually inserts rows into the table and doesn’t place a metadata lock.

More reading on metadata locks and how to troubleshoot them in MySQL 5.7: 

https://www.percona.com/blog/2016/12/28/quickly-troubleshooting-metadata-locks-mysql-5-7/

You may also like:

Clickhouse is a fast, easy to pick up, open-source DBMS for online analytical processing. It can be integrated with your applications that already use MySQL. However, Clickhouse does not support Update/Delete yet. Don’t fret, as Percona’s Jervin Real has detailed a clever stratagem for updating and deleting rows by implementing incremental refresh on a Clickhouse table. Read his article for a high-level overview of the process.

MySQL GROUP BY could be responsible for 90% or more of your database’s query execution time. Percona Founder and CEO, Peter Zaitsev, breaks down the four ways MySQL executes GROUP BY statements and details when the query may not be the most optimal course of action.

&nbsp;