Storage fluctuates when creating a table as select from another table via python

by Andrew   Last Updated May 22, 2020 19:06 PM

My table "test_table2" holds around 12mil records and has 6 columns with small records (short strings, dates, etc.). The size of the table is cca 1 GB. When I'm running this query:

query = """create table db.table1 
        as (
        select id, string1, date1, gpslon, gpslat, timestamp  
        from db.test_table2

from jupyter notebook with python, I can see in the Storage Management tools (macos), that the free space is lowered by 1 GB and after the creation of the "table1", the free space raises back again (cut by the size of the new table).

The size of the "table1" is cca 630 MB.

How this all works? Does the whole "test_table2" need to be stored temporarily when creating another table as select from this table? Can this be managed somehow? Because when I was trying to create a table as select from a bigger one than 1 GB, the process consumed all free space and everything failed consequently.

Thanks for any help or explanation!

Related Questions

getting innodb buffer usage

Updated August 24, 2018 15:06 PM

Connection pooling with python mysql-connector

Updated March 27, 2017 17:06 PM