Every table is a logical object in a database; but it also needs to physically store its data (records) on the disk and/or in memory. Tables use a Storage Engine to do this. SE are plugins which can be installed or uninstalled into the server (if they're not builtin).
Many operations are requested by the server but physically done by the SE. So, from the SE we choose for a table affects performance, stability, LOCKs type, use of the query cache, disk space required and special features.
In some future versions of MySQL, partitioned tables will be able to use different SE for different partitions.
Let's see which Storage Engine is good for which uses.
| A Study Guideian suggests that this study guide or chapter be merged into MySQL/Optimization.
Please discuss whether or not this merge should happen on the discussion page.
MyISAM does table level locking, while InnoDB does row level locking. In addition to foreign keys, InnoDB offers transaction support, which is absolutely critical when dealing with larger applications. Speed may suffer, particularly for inserts with full transaction guarantees, because all this Foreign Key / Transaction stuff adds overhead.
The default table type for MySQL on Linux is MyISAM, on Windows, normally InnoDB. MyISAM uses table level locking, which means during an UPDATE, nobody can access any other record of the same table. InnoDB however, uses Row level locking. Row level locking ensures that during an UPDATE, nobody can access that particular row, until the locking transaction issues a COMMIT. Many people use MyISAM if they need speed and InnoDB for data integrity.
Overall, InnoDB should be used for with applications that rely highly on data integrity or need transactions, while MyISAM can be used where that is not required or where fulltext indexing is needed. Where speed is more important, both should be tried because which is faster depends on the application.
Drizzle, a MySQL's fork supported by Sun Microsystems, uses InnoDB as its default engine and doesn't support MyISAM.
Synonyms: Merge, MRG_MYISAM
CREATE TABLE mumbai (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM CREATE TABLE delhi (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM CREATE TABLE total (first_name VARCHAR(30), amount INT(10)) TYPE=MERGE UNION=(mumbai,delhi)
Merges can be used to work around MySQL's or system's filesize limits. In fact those limits affect single MyISAM datafiles, but don't affect the whole Merge table, which doesn't have a datafile.
In the past, in some cases Merge and MyISAM could be used to replace views, which were not supported by MySQL. Merge could be used as a base table and MyISAM tables could be used as views containing part of the base table data. A SELECT on the Merge table returned all the effective data. View support was added in MySQL 5.0, so this use of Merge tables is obsolete.
HEAP is the name of this table type before MySQL 4.1. MEMORY is the new, preferred name.
This engine is introduced in version 3.23.
Synonyms: BDB, BerkleyDB
BDB has been removed from version 5.1 and later due to lack of use.
BerkeleyDB is a family of free software embeddable DBMS's developer by SleepyCat, a company which has been acquired by Oracle. SleepyCat provided a Storage Engine for MySQL called BDB.
BDB supports transactions and page-level locking, but it also has many limitations within MySQL.
Discards all data stored in it but does still write to the binary log, so it is useful in replication scale-out or secure binlog-do filtering situations where slaves aren't trustworthy and for benchmarking the higher layers of the server.
For completeness, other storage engines include:
You can get metadata about official MySQL Storage Engines and other Storage Engines which are present on your server, via SQL.
Starting from MySQL 5.0, you can get information about the Storage Engine which you can use using the SHOW STORAGE ENGINES statement.
SHOW STORAGE ENGINES
The STORAGE word is optional. This command returns a dataset with the following columns:
`ENGINES` is a virtual table within the INFORMATION_SCHEMA database. It can be used to get information about Storage Engines. Its columns are the came which are returned by the SHOW ENGINES statement (see above).
ENGINES has been added in MySQL 5.1.5.
If you want more info about an official MySQL Storage Engine, you can use the HELP command:
If you are using the command line client, you can omit the quotes:
help myisam \g
When you want to create a table using a given Storage Engine, you can use the ENGINE clause in the CREATE TABLE command:
CREATE TABLE ... ENGINE=InnoDB
If the ENGINE clause is not specified, the value of the storage_engine variable will be used. By default it's MyISAM, but you can change it:
Or you can modify the value of default-storage-engine in the my.cnf before starting the MySQL server.
You can also change the Storage Engine of an existing table:
ALTER TABLE `stats` ENGINE=MyISAM
mysql_convert_table_format is a tool provided with MySQL, written in Perl. It converts all the tables contained in the specified database to another Storage Engine.
The syntax is:
mysql_convert_table_format [options] database
database is the name of the database in which the program will operate. It's mandatory.
--help Print a help and exit.
--version Print version number and exit.
--host=host The host on which MySQL is running. Default: localhost.
--port=port TCP port.
--user=user Specify the username.
--password=password Specify the password. As it is insecure (it's visible with the coomand top, for example), you can use an option file, instead.
--type=storage_engine The storage engine that the tables will use after conversion.
--force Don't stop the execution if an error occurs.
--verbose Print detailed information about the conversions.
mysql_convert_table_format --host=localhost --user=root --password=xyz970 --force --type=InnoDB test
This command specifies access data (localhost, username, password) and converts all tables within database `test` into InnoDB. If some tables can't be converted, the script skips them and converts the others (--force). Italic text
Manage research, learning and skills at NCR Works. Create an account using LinkedIn to manage and organize your omni-channel knowledge. NCR Works is like a shopping cart for information -- helping you to save, discuss and share.