MySQL Database Storage Engines
MySQL supports a number of different storage engines, sometimes also called table types. This means that you have a choice about the underlying implementation of the tables, each table in your database can use a different storage engine, and you can easily convert between them.
You can choose a table type when you create a table by using
CREATE TABLE table TYPE = type…
The commonly available table types are:
This type is the default and is the storage engine you should use for most applications. These tables are transaction safe; that is, they provide COMMIT and ROLLBACK capabilities, InnoDB tables also support foreign keys. They have the best read write performance, at least partly because they support row-level locking.
This type used to be the default in older versions of MySQL. It is based on the traditional ISAM type, which stands for Indexed Sequential Access Method, a standard method for storing records and files. MyISAM adds a number of advantages over the ISAM type. MyISAM tables can be compressed, and they support full text searching. They are not transaction safe and do not support foreign keys. They can outperform InnoDB on low-read or read-only applications, but because they use table-level locking, they do not perform as well as InnoDB on read-write applications.
(previously known as HEAP) – tables of this type are stored in memory, and their indexes are hashed. This makes MEMORY tables extremely fast, but, in the event of a crash, your data will be lost. These characteristics make MEMORY tables ideal for storing temporary or derived data for read-heavy applications. They support table-level locking so are not ideal for a write-heavy or mixed read-write workload. They cannot have BLOB or TEXT columns.
These tables allow you to treat a collection of MyISAM tables as a single table for the purpose of querying systems.
These tables store large amounts of data but with a small footprint. Tables of this type support only INSERT and SELECT queries, not DELETE, UPDATE, or REPLACE. Additionally, indexes are not used.
These tables are stored on the server in a single file containing comma-separated values. The benefit of these types of tables only appears when you need to view or otherwise work with the data in an external spreadsheet application such as Microsoft Excel.
In most web applications, you will almost always use InnoDB tables.
You should always use InnoDB when transactions are important, such as for tables storing financial data or for situations in which INSERTS and SELECTS are being interleaved, such as online message boards or forums. You should also always use it when maintaining referential integrity (via foreign keys) is important, which is the case in most applications that require a relational database.
We may choose to use MyISAM in some cases. The typical use of MyISAM is in a dataware-housing application. Also, at the time of writing, the full-text indexing support was more advanced in MyISAM than it was in InnoDB, but InnoDB is likely to catch up in the near future.
You can use MEMORY tables for temporary tables or to implement views, and MERGE tables if you need to deal with very large MyISAM tables.
You can change the type of a table after creation with an ALTER TABLE statement, as follows:
ALTER TABLE Orders ENGINE = innodb;
ALTER TABLE Order-Items ENGINE = innodb;
stay tunned with codejow !