linux poison RSS
linux poison Email

How to Change MySQL Storage Engines

MySQL 5.0 and higher offers nine storage engines and more are likely to be added in the future. The most commonly used are MyISAM, InnoDB, and Berkeley DB (BDB). Each storage engine offers special features and advantages. You can even use different formats for each table in your database, though it may be harder to manage a mixed format database. Better is to keep all tables in a database using the same storage engine, but use different engines for different databases.

To determine which storage engines your server supports, run following SHOW ENGINES; statement. The value in the Support column indicates whether an engine can be used. A value of YES, NO, or DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine.

MyISAM: 
The default MySQL storage engine and the one that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default. MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations.

InnoDB: 
A transaction-safe (ACID compliant) storage engine for MySQL that has commit, roll-back, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent non-locking reads increase multi-user concurrency and performance.

Convert from one type of engine to other.
When you create a new table, you can specify which storage engine to use by adding an ENGINE or TYPE table option to the CREATE TABLE statement:
CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MYISAM;
To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine:
ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = INNODB;
The above statement would change your MySQL storage Engine.
NOTE: Dont’ forget that it takes a lot of computer resource to convert large tables.


1 comments:

aman said...

its so nice educational post.thanx for sharing such a quality knowledge.

Post a Comment

Related Posts with Thumbnails