Most of us work with phpMyAdmin, Mysql Query Browser, Mysql Migration Tool, Mysql Administrator where we see the database and tables with their logical view. How many of us have see the physical structure of our db?. When the term physical comes, several question arises:

1) where do they reside?
2) what sort of file structure they possess?
3) how can we check it?
4) What are .frm, .myi, .myd inside them?
5) Are they going to differ for different Engines we use?

Generally you can find the data directory of your file with your .my.cnf file. The physical structure of db is some thing like a directory structure, where each database is a subdirectory under the main directory and has some files in it. Each table has its own file. Bascially one can see three types of files .frm, .myi, .myd.

But they are not same for all tables and database. They differ based on the engines you use and sometimes even differ with the OS (Operating System). There are lots of other factors that is in the backend behind the type of files you see. We will see some basic differences.

• .FRM: It has the table structure of your table or table definition
• .MYI: It has the indexes of your table
• .MYD: It contains your data

For example: if your db name is school and tables called class and student. The physical structure will have a directory called school and files class.frm, class.myi, class.myd, student.frm, student.myi, student.myd.

Engines Specific

Lets consider table student belongs to innodb and table class has MyIsam:

Innodb: Innodb has only .frm files and it has its own tablespace where it contains indexes and datas and its shared in databases. for example: student.frm.

MyISAM: MyIsam has all the three files. where .myi has your indexes, .myd has your table datas and .frm has its table definition. For example: class.frm, class.myi, class.myd.

Need this: you can use these files when your db crash, or when you upgrade your db to another version and it can also be used while migrating and repairing your indexes without affecting data.