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?
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With