Activities

October 2012
M T W T F S S
« Sep   Nov »
1234567
891011121314
15161718192021
22232425262728
293031  

MySQL : How to convert all the table’s Storage Engine type

I was doing some investigation to fix the slow performance of a server. After few hours of verious optimization, I see that after enabling general querry log, mysql is talking little time for I/O operations which is affecting the query execution time.

I found a very nice bash command did it nicely …

 mysql  -B -N -e 'show tables' databasename | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;"

is a good one .. 🙂

2 comments to MySQL : How to convert all the table’s Storage Engine type

  • I have long not been developing php but when I active again with php I found there is a change in mySQL especially ENGINE. Why mySQL change TYPE to ENGINE? and what difference for MyISAM or InnoDB ENGINE such as?

    • Thanks for the reading,

      The major benefits of using InnoDB is it’s support transaction. So that you can revert the erroneous query execution without affecting the database. Moreover it’s supports row-level locking, foreign keys and good for managing high volume, high performance type record sets.

      MyISAM poorly used for text based data ie managing for text based content of huge record set. It’s uses disk for operations where InnoDB uses memory for fast operations.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>