Different types of storage engine in MySQL

[Spy$Eyes]

MySQL allows you to use many different types of tables, or other "storage engine" (storage engines) for storing data. The most important distinction between the different systems is that between transactional and not transactional. The transactional engines offer some important advantages: they are safer (allowing you to recover data even if MySQL crashes or hardware problems) and allow for multiple changes and validate them all together or, conversely, to restore the previous situation if something goes wrong. For their part, non-transactional engines have the advantage of greater speed, use less disk space and less memory requirements for the update. You can also combine transactional tables and not in the same instructions, although in this case, changes made on non-transactional tables, however, become effective when they are executed. When you create a table you specify what kind of MySQL is using the option ENGINE:

CREATE TABLE table (a INT) ENGINE = INNODB;

In case the declaration is not specified, MySQL will use the default type, which is usually MyISAM. However, if you installed MySQL on Windows through the Configuration Wizard, the default type that will be set to InnoDB. You can change this value by acting on the option - default-table-type in the configuration file. If you try to create a table type not supported by your database (such as the engine is not included on your server or because you have started without that system), MySQL will create a MyISAM table instead.

MyISAM : This is the default MySQL storage engine, based on the old ISAM no longer supported. Each MyISAM table uses three files: a file .Frm that contains the table definition, plus a file .MYD for data and a file .MYI for indexes. You can index the BLOB and TEXT columns, and you can use NULL values in indexed columns. MyISAM also can handle a AUTO_INCREMENT column per table, increasing its value automatically for each line of writing, the deleted values are not reused even if they're the last sequence. Each column can use a different character set. MyISAM tables may be at static, dynamic or compressed. The static format is used when the table does not contain variable length columns (VARCHAR, BLOB, TEXT) and offers greater security and speed, but generally requires more disk space. The dynamic format, however, can easily lead to a fragmentation of the table (in case of multiple amendments or deletions of data), in which case you should periodically perform an optimization using the command OPTIMIZE TABLE. Note that if a table is dynamic in size, columns defined as CHAR is converted to VARCHAR unless they have a length not greater than 4 bytes. The compressed format is then used to generate a read-only tables that minimize the occupation of space, are created by the utility myisampack. If problems are found on a table, you must use the CHECK TABLE command to verify the situation, and REPAIR TABLE to reset the table. MySQL is able to solve most problems with this system.

[Spy$Eyes]

InnoDB :

It is a transactional storage engine with a capacity to commit, rollback and crash recovery. It is optimized for concurrent use of data among many users and for very high performance even with large amounts of data. It also supports the foreign key. If you do not use InnoDB tables, you can start the server with the - skip-innodb. But if you use them, you should give the server the information on file to use for the data. Here is an example of configuration options related to the InnoDB option file:

innodb_data_home_dir = / ibdata 
innodb_data_file_path = ibdata1: 50M; ibdata2: 50M: autoextend: max: 500M 
innodb_buffer_pool_size = 90M 
innodb_additional_mem_pool_size = 10M 
innodb_log_group_home_dir = / iblogs 
innodb_log_files_in_group = 2 
innodb_log_file_size = 20M 
innodb_log_buffer_size = 8M 
innodb_flush_log_at_trx_commit = 1 
innodb_lock_wait_timeout = 75 
skip-external-locking 
max_connections = 100 
read_buffer_size = 1M 
sort_buffer_size = 1M

innodb_data_home_dir indicates the directory for data files. If not specified, InnoDB uses the MySQL data directory. If the set is empty, you must specify the full path to innodb_data_file_path, indicating the names of files that will be used as a tablespace. You can specify one or more: the example we have a file ibdata1 of 50 megabytes and 50 megabytes of a file ibdata2 which may extend up to 500. Autoextend option may be given only on the last file. Please note the maximum size of the files on your system to indicate the max parameter. The specified directory must exist, because the server can not create them. innodb_buffer_pool_size should be about half the memory of your computer.

innodb_log_file_size should be about 25% of the size of the buffer pool. The other settings should be fine as is. If you do not set the configuration settings, InnoDB will create a data file of 10MB and two 5MB log files from each directory in the MySQL database. We said that InnoDB supports the use of transactions. Each connection to the MySQL server will start in autocommit mode, which means that all the update statements are made effective immediately. If you turn off the autocommit with SET AUTOCOMMIT = 0, the amendments will become operative only when you execute the COMMIT statement. If instead performed ROLLBACK will undo all changes to the previous COMMIT. You can use transactions without disabling the autocommit, beginning with START TRANSACTION or BEGIN and ending with COMMIT or ROLLBACK. Manages the InnoDB AUTO_INCREMENT value for a table in a special way: this is in fact calculated the first time that it becomes necessary after starting the server (eg INSERT), selecting the maximum value that exists on the table is incremented by 1. At that point the value is stored in memory but not written to disk, so the next reboot will be recalculated. This means that if you delete the last values of the table without making new entries, restart the server to reuse those values. With InnoDB tables we can define the foreign key, or connecting the values of the columns that contain keys to other tables to the tables. In this way you can check automatically when the values of the parent table is modified or removed so as to prevent these changes or, conversely, to amend the values on the child table. Also you can not enter values in the child table that have no counterpart in the parent table. Let's see an example definition:

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
 ) ENGINE = INNODB;

 CREATE TABLE child (id INT, INT mother_id,
                    INDEX par_ind (mother_id)
                    FOREIGN KEY (mother_id) REFERENCES parent (id)
                      ON DELETE CASCADE
 ) ENGINE = INNODB;

This code defines two tables where we see the child table has a foreign key on the parent table. When deleting a row from parent table if the value of id is present in a field mother_id daughter of the table the row is also deleted. Both tables must be InnoDB type, both columns must appear as the first field of an index. You can name the report stating "CONSTRAINT name FOREIGN KEY before. This name will then be used if you decide to delete the foreign key, with the syntax "ALTER TABLE table DROP FOREIGN KEY name". If the name is not specified, will be created automatically by the server and view it through "SHOW CREATE TABLE table". In the example we saw an indication of the ON DELETE clause to handle cancellations on the foreign key referenced, so it is possible to use ON UPDATE to manage updates. Both options accept as one of the following possible values: CASCADE, SET NULL, RESTRICT, NO ACTION.

With the former, as we have seen, the changes on the parent table are reported on the child table. With SET NULL values in any child table are set to NULL (the definition of the column should allow). The last two are equivalent to omit the clause, leading to the rejection of the amendment on the parent table. In a table, InnoDB is not possible to use FULLTEXT indexes, and columns of geometric type are only available since version 5.0.16. For AUTO_INCREMENT columns must exist an index of which they are the only component.

[Spy$Eyes]

MERGE :

A MERGE table is formed by a set of identical MyISAM tables to each other in the structure. This means that they must have the same columns and the same indexes, even the order in which columns and indexes are declared to be the same. Here's an example definition:

CREATE TABLE total (
   a INT NOT NULL AUTO_INCREMENT,
   message CHAR (20), INDEX (a))
   ENGINE = MERGE UNION = (t1, t2) INSERT_METHOD = LAST;

The UNION clause specifies the names of the tables are the totals table. These tables will be set the same fields and the same indexes, the only exception being the primary key that is not replicated in the MERGE table as the uniqueness of the keys can only be guaranteed within a single table. With INSERT_METHOD LAST = specifies that any placement must be made on the last set of tables, the alternative would be the FIRST option to make your entries on the first. If the clause is not specified, you can not insert records on the table. Note that if you run the DROP TABLE on a MERGE table with only the latter will be eliminated, not the underlying tables. The tables of this type can be useful when dealing with large amounts of data that is physically separate but prefer to keep that all together should be consulted if necessary (eg log tables). The use of MERGE tables can give performance advantages compared to the case where all data resides on a single table.


MEMORY :

MEMORY tables (formerly called HEAP) have a definition that is saved to disk, but their data is stored only in memory. This means that each time you reboot the server those tables will be empty. Of course, these tables will be very quick. But we must be careful not to create tables that take too big, or you can send a blow to the server. The system variable max_heap_table_size sets a limit to their size. MEMORY tables can not use fields of type BLOB or TEXT.


BDB (BerkeleyDB) :

BDB tables are not working on Linux Alpha, AMD64, IA-64, s390, as well as on Mac OS X. They operate instead on Intel Linux platforms, as well as Sun Solaris, FreeBSD, AIX 4.3.x, SCO (OpenServer and UnixWare 7.1.x), Windows (NT architecture and up). If you can not or do not want to use BDB tables, you can start the server with the - skip-bdb. Note however that this engine is not always available: for example, the Windows binary distributions include it only in MySQL-Max. BDB tables are transactional, as already seen for InnoDB, BDB works in default autocommit, and you can change its behavior with SET AUTOCOMMIT = 0, or with a BEGIN WORK to start a transaction that is suspending the autocommit. COMMIT or ROLLBACK are the possible conclusions of the transaction.

You can specify in the configuration files a few options for BDB. For example the - bdb-home and - bdb-logdir respectively specify the directory for data and log files. If there is a unique index on a column that is nullable, a NULL value only for that column will be present on the table. BDB tables are made from a file. Frm the table definition and a. Db file that contains data and indexes. These files can not be moved because the. Db file also contains its own path.


ARCHIVE :
The ARCHIVE table type, as the name suggests, can be used to store large amounts of data without indexes, and without the option to edit but just to add rows to the table. On these tables it is possible to make only SELECT and INSERT. The data is compressed using zlib, and therefore their occupation of space is very limited.


CSV :

This storage engine is used to store data to a text file with values separated by commas. A table is formed from a CSV file .Frm that, as usual, describes the table, and the .CSV file that contains data in text format. is intuitive that this type of table that does not support indexes, can be useful if you want to export data to applications like spreadsheets.