CREATE TABLE as SELECT - using MEMORY ENGINE (in RAM memory)

mysql

Solution

Yes, it can be done, but not with simple syntax `CREATE TABLE t AS SELECT ...` - you will need to specify table columns in full statement, Sample:

CREATE TABLE t (col1 INT(11), col2 INT(11)) 
ENGINE=MEMORY 
AS 
SELECT * FROM another_t

Maximum size of table by default if `16Mb`, but it can be adjusted with `max_heap_table_size` server system variable. But please, note, that this limit is per engine - not per table. I.e. all your `memory` tables will share it. To restrict size of individual table, you'll need to operate on session value for `max_heap_table_size`, like

 mysql> SET max_heap_table_size = 24*1024*1024;

Problem

I was reading about tables using `MEMORY ENGINE` (tables stored in ram). Is it possible to use `CREATE TABLE AS SELECT` syntax, but have the created table us `MEMORY ENGINE`? Is there a limit to the size of a MEMORY table? Could create a MEMORY table that is a copy of a pysical 1.5 GB table?

Original source