What happened when you add a table in your MysQL database

Whenever you add a table to a database, one or more of the following types of files are created in the database subdirectory (a directory is created which name is same as name of the database).

1. .frm - The primary table-related file that is used to define the table's format. All table types have an associated .frm file.
2. .MYD - The file that stores the data contained in several table types.
3. .MYI - An index file used by several table types.
4. .MRG - A special type of file that is used to list the name of merged tables.

To define a table type, we must include an Engine clause at the end of  table definition, after the parentheses that enclose table elements.
Engine = { BDB | MEMORY | ISAM | INNODB | MERGE | MYISAM }
For example,

CREATE TABLE AuthorBios

{

   AthoID SMALLINT UNSIGNED NOT NULL,

   YearBBorn YEAR NOT NULL,

   CityBorn VARCHAR(40) NOT NULL DEFAULT 'Unknown'

}Engine=INNODB;

It is not required that for every table type (storage engine), all files will create. Below is the details about it.

Table Type                                                                             Files used

BDB                                                                                         .frm,.MYD,.MYI

MEMORY                                                                               .frm

InnoDB                                                                                     .frm

ISAM                                                                                       .frm,.MYD,.MYI

MERGE                                                                                    .frm,.MRG

MyISAM                                                                                  .frm,.MYD,.MYI