Physical Mysql - what are these? (.FRM, .MYI, .MYD). Need this?

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 db. They differ based on the engines you use and sometimes even differ with the os. 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 ex: 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 ex: 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. 

ex: 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..

 

Bits and pieces

 

   This is for those who are not aware about engines. Apart from Innodb and MyIsam there are also some other engines in mysql such as Merge, Memory, Cluster etc..

How to find and change the engines of MySQL tables

Mostly programmers use two types of engines for MySQL DB. They are Innodb, MyISAM. You can check this details very easily in PHPMyAdmin. But if you are in MySQL interactive mode in command prompt. you cant get the details that easily from your DB. But the details of your DB, Tables etc .. are maintained in a db called information_schema. So in order to fetch the details of the engines being used type the following query:

 

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name];

 

This will list all your tables with their respective engines being used. Incase you know table_name, you can add that as a condition to your query

 

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name];

 

 

To change the Engines

 

Alter table table_name Engine=[engine you want to change]

Some Interesting statement terminators

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name]\g

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = [db_name] and TABLE_NAME=[table_name]\G

 

Instead of semicolon, use \g and \G and see the difference :)

Deleting Duplicate Data In MySQL

For deleting duplicate rows in a MySQL table if we are writing the query as folows

 DELETE FROM table_name
where column_name in (
    SELECT  column_name
    FROM table_name
    GROUP BY column_A, column_B, column_C, ... 
    having COUNT(*)>1);

 then it will show

Error Code: 1093. You can't specify target table 'supplier_matrix' for update in FROM clause

It is required add a temporary table otherwise it will throw error.

Following query first selects the duplicate rows and creates a temporary table temp and the deletes the duplicate rows.

 

DELETE FROM table_name 
where column_name in(
    SELECT  column_name
        FROM (
                select column_name
                FROM table_name
                GROUP BY column_A, column_B, column_C, ...
                having COUNT(*)>1)temp
                );

Mysql Restrictions

http://architects.dzone.com/news/10-things-mysql-won%E2%80%99t-work

Technology Needs to know

5th jun 2013 4:41 Pm

1. R Programming language
        http://www.r-project.org/
2. Python
3. SSAS
4. SSIS    
      https://www.simple-talk.com/sql/ssis/
          http://msdn.microsoft.com/en-us/library/ms140234%28v=sql.105%29.aspx 
           http://www.sql-server-performance.com/2009/SQL-Server-2008-Integration-Services-Tasks/ 
5. SSRS
6. Talend
http://www.talend.com/resources/webinars

7. Mysql DBA
8. MsSql DBA

Form submitting using javascript: works for all the browsers

<form>
<input type="submit" onclick="Close()" value="Submit">
</form>

function Close(){

document.forms0.submit(); 

} 

Open source Reporting scripts

http://www.highcharts.com/demo/pie-basic