Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Storage Engines: MyISAM, InnoDB and BDB

By: FYIcenter.com

Part:   1   2  3  4  5  6 

A collection of 15 tutorial tips on MySQL storage engines. Clear explanations and tutorial exercises are provided on creating tables with MyISAM, InnoDB, and BDB storage engines; checking data files of different storage engines; using MEMORY storage engine for temporary tables. Topics included in this collections:

  1. What Are Storage Engines?
  2. How To Create a New Table Using MyISAM Storage Engine?
  3. Where Table Data Is Stored by the MyISAM Storage Engine?
  4. How To Backup Tables by Copying MyISAM Table Files?
  5. How To Restore Tables by Copying MyISAM Table Files?
  6. How To Check and Repair MyISAM Tables?
  7. How To Create a New Table Using the InnoDB Storage Engine?
  8. Where Table Data Is Stored by the InnoDB Storage Engine?
  9. How To Create a New Table Using the BDB Storage Engine?
  10. How To Start mysqld to Support the BDB Storage Engine?
  11. Where Table Data Is Stored by the BDB Storage Engine?
  12. How To Create a New Table Using the CSV Storage Engine?
  13. How To Create a New Table Using the MEMORY Storage Engine?
  14. What Happens to MEMORY Tables When MySQL Server Is Stopped?
  15. How To See Which Storage Engines Are Supported in Your MySQL Server?

Please note that all answers and tutorials are based on MySQL 5.0. Sometimes you may need to run previous tutorials in order to continue a later tutorial.

What Are Storage Engines?

Storage engines are programs that are integrated into MySQL database management system to manage data tables. MySQL 5.0 supports the following major storage engines:

  • MyISAM Storage Engine - MySQL default storage engine. Based on ISAM database concept. MyISAM is not transaction safe.
  • InnoDB Storage Engine - A transaction safe storage engine developed by Innobase Oy (an Oracle company).
  • BDB (BerkeleyDB) Storage Engine - A transaction safe storage engine originally developed at U.C. Berkeley. Sleepycat Software, Inc. was created in 1996 to continue BDB development. Sleepycat is an Oracle company now.
  • CSV Storage Engine - A simple storage engine storing data as text files using comma-separated values format.
  • MEMORY (HEAP) Storage Engine Storage Engine - A storage engine storing data in computer main memory.

How To Create a New Table Using MyISAM Storage Engine?

MyISAM storage engine is based on the ISAM (Indexed Sequential Access Method) concept, which was first developed at IBM to store and retrieve data on secondary storage systems like tapes. MyISAM storage engine offers fast data storage and retrieval. But it is not transaction safe.

MyISAM is the default storage engine. All new tables will be created with MyISAM storage engine if you do not specify any storage engine name. But if you want to create a new table with MyISAM storage engine explicitly, you can specify "ENGINE = MYISAM" as the end of the "CREATE TABLE" statement. The tutorial exercise below shows you a good example:

>cd \mysql\bin
>mysql -u dev -piyf

mysql> USE fyi; 

mysql> CREATE TABLE fyi_isam (
  id INTEGER PRIMARY KEY,
  title VARCHAR(80), 
  count INTEGER )
  ENGINE = MYISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW CREATE TABLE fyi_isam;
  CREATE TABLE `fyi_isam` (
  `id` int(11) NOT NULL,
  `title` varchar(80) default NULL,
  `count` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
1 row in set (0.02 sec)

Where Table Data Is Stored by the MyISAM Storage Engine?

By default, MySQL provides \mysql\data directory for all storage engines to store table data. Under \mysql\data directory, each database will have its own subdirectory to store table data.

If a new table is created with the MyISAM storage engine, several files will be created. For example, the new table "fyi_isam" created in the previous tutorial exercise will result the following files:

>cd \mysql\data\fyi

>dir
   8,620 fyi_isam.frm
       0 fyi_isam.MYD
   1,024 fyi_isam.MYI
...

These files are:

  • fyi_isam.frm - Created by MySQL to store "fyi_isam" table format.
  • fyi_isam.myd - Created by MyISAM to store "fyi_isam" table data. "myd" stands for "MyData". The data file size is 0, because there is no data row stored in the table yet.
  • fyi_isam.myi - Created by MyISAM to store "fyi_isam" table index. "myi" stands for "MyIndex". The primary key column "id" is indexed by default.

(Continued on next part...)

Part:   1   2  3  4  5  6 


Selected Developer Jobs:

More...