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

MySQL FAQs - Storage Engines: MyISAM, InnoDB and BDB

By: FYIcenter.com

Part:   1  2  3   4  5  6 

(Continued from previous part...)

How To Check and Repair MyISAM Tables?

If you have a corrupted MyISAM table, like the one resulted from the previous tutorial exercise, you can use the "CHECK TABLE" and "REPAIR TABLE" commands to try to repair it. The following tutorial exercise gives you a good example of repairing a corrupted MyISAM table. Luckily, the table is repaired successfully:

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

mysql> CHECK TABLE fyi_isam;
+--------------+-------+----------+-------------------------
| Table        | Op    | Msg_type | Msg_text                
+--------------+-------+----------+-------------------------
| fyi.fyi_isam | check | error    | Record at pos: 0 is not
                                       remove-marked
| fyi.fyi_isam | check | error    | record delete-link-chain
                                       corrupted
| fyi.fyi_isam | check | error    | Corrupt                 
+--------------+-------+----------+-------------------------
3 rows in set (0.01 sec)

mysql> REPAIR TABLE fyi_isam;
+--------------+--------+----------+------------------------
| Table        | Op     | Msg_type | Msg_text               
+--------------+--------+----------+------------------------
| fyi.fyi_isam | repair | warning  | Number of rows changed
                                        from 1 to 2
| fyi.fyi_isam | repair | status   | OK                     
+--------------+--------+----------+------------------------
2 rows in set (0.47 sec)

mysql> SELECT * FROM fyi_isam;
+----+-------+-------+
| id | title | count |
+----+-------+-------+
| -1 | NULL  |  NULL |
| -2 | NULL  |   987 |
+----+-------+-------+
2 rows in set (0.00 sec)

How To Create a New Table Using the InnoDB Storage Engine?

InnoDB storage engine was developed by Innobase Oy, which is an Oracle company now. InnoDB is transaction safe, and has been used by a number of large Websites, like Slashdot.org.

InnoDB is not the default storage engine. You need to specify "ENGINE = InnoDB" at the end of the "CREATE TABLE" statement to create new tables with InnoDB storage engine. The tutorial exercise below shows you a good example:

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

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

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

mysql> INSERT INTO fyi_inno (id) VALUES(-1);
Query OK, 1 row affected (0.05 sec)

Where Table Data Is Stored by the InnoDB Storage Engine?

By default, MySQL provides \mysql\data directory for all storage engines to store table data. Under \mysql\data directory, InnoDB storage engine will create 3 files to store and manage all tables that use the InnoDB storage engine:

• ibdata1 - Contains data for all InnoDB tables. "ibdata1" starts with 10MB, but it will be extended, if needed.
• ib_logfile0 - Log file 1 with 5MB.
• ib_logfile1 - Log file 2 with 5MB.

If a new table is created with the InnoDB storage engine, one format file named as databaseName\tableName.frm will be created. Table data and index data will be stored in "ibdata1". For example, the new table "fyi_inno" created in the previous tutorial exercise will result the following files:

>cd \mysql\data

>dir fyi\fyi_inno.*
   8,620 fyi_inno.frm

>dir 
   10,485,760 ibdata1
    5,242,880 ib_logfile0
    5,242,880 ib_logfile1
...       

(Continued on next part...)

Part:   1  2  3   4  5  6