MEMORY storage engine
The MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.
MEMORY storage engine creates a table in memory only, this allows for extremely fast data access, however the drawback is that data is not persisted across reboots, also if you replicate this table to a slave server, it will also truncate the slave table as well even though it did not restart. If rows are deleted from a memory table you must use either alter table or optimize table to defrag the table and reduce the amount of memory used.
The MEMORY storage has the following features:
- All data is stored in memory
- Non-transactional
- No foreign key support
- very fast read and write activity due to being entirely in memory
- table-level locking
- A MEMORY table can include yo to 32 indexes per table
- implements both hash and b-tree indexes
- tables use a fixed length row storage format and thus cannot be defined with data types of blob or text
The MEMORY storage engine has a few parameters that you can tweak
max_heap_table_size - the maximum size of MEMORY tables is limited by the system variables, default is 16MB
init_file - this can be used to specify a file to use to populate a MEMORY table when mysqld starts
CSV Storage Engine
The CSV storage engine stores data in text files using comma-separated values format.The CSV storage engine is always compiled into the MySQL server. To examine the source for the CSV engine, look in the storage/csv directory of a MySQL source distribution.
When you create a CSV table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in comma-separated values format.
CSV storage has the following features:
- Plain text data in CSV format
- Easily imported into programs such as Microsoft Excel
- Table-level locking
- No foreign key support
- Non-transactional
- Trivial backups and restores (just copy the files)
- Does not support indexes or partitioning
Three files are created:
.frm which contains the table format
.CSM which contains metadata and lastly
.CSV file which contains the data.
The MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.
MEMORY storage engine creates a table in memory only, this allows for extremely fast data access, however the drawback is that data is not persisted across reboots, also if you replicate this table to a slave server, it will also truncate the slave table as well even though it did not restart. If rows are deleted from a memory table you must use either alter table or optimize table to defrag the table and reduce the amount of memory used.The MEMORY storage has the following features:
- All data is stored in memory
- Non-transactional
- No foreign key support
- very fast read and write activity due to being entirely in memory
- table-level locking
- A MEMORY table can include yo to 32 indexes per table
- implements both hash and b-tree indexes
- tables use a fixed length row storage format and thus cannot be defined with data types of blob or text
The MEMORY storage engine has a few parameters that you can tweak
max_heap_table_size - the maximum size of MEMORY tables is limited by the system variables, default is 16MB
init_file - this can be used to specify a file to use to populate a MEMORY table when mysqld starts
CSV Storage Engine
The CSV storage engine stores data in text files using comma-separated values format.The CSV storage engine is always compiled into the MySQL server. To examine the source for the CSV engine, look in the storage/csv directory of a MySQL source distribution.
When you create a CSV table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in comma-separated values format.
CSV storage has the following features:
- Plain text data in CSV format
- Easily imported into programs such as Microsoft Excel
- Table-level locking
- No foreign key support
- Non-transactional
- Trivial backups and restores (just copy the files)
- Does not support indexes or partitioning
Three files are created:
.frm which contains the table format
.CSM which contains metadata and lastly
.CSV file which contains the data.
No comments:
Post a Comment