≡ Menu

What is InnoDB and MyISAM and how to Convert MySql Database Tables from InnoDB to MyISAM

The following is a table comparison between InnoDB with MyISAM

                                             My ISAM   InnoDB
Required full text Search                      Yes
Require Transactions                                    Yes
frequent select queries                        Yes
frequent insert,update,delete                           Yes
Row Locking (multi processing on single table)          Yes
Relational base design                                  Yes

There is minor problem that MyISAM did not support Transactions and innoDB are more relialbe on atomicity. But you can solve that problem by your own programmming design, you can achieve that by random no method, you can achieve parallelism also

Here is a simple shell script to automatically Convert MySql Database from InnoDB to MyISAM.

#!/bin/bash

MYSQLCMD=mysql

for db in 'echo show databases | $MYSQLCMD | grep -v Database'; do
        for table in 'echo show tables | $MYSQLCMD $db | grep -v Tables_in_'; do
                TABLE_TYPE='echo show create table $table | $MYSQLCMD $db | sed -e's/.*ENGINE=([[:alnum:]]+)[[:space:]].*/1/'|grep -v 'Create Table''
                if [ $TABLE_TYPE = "InnoDB" ] ; then
                        mysqldump $db $table > $db.$table.sql
                        echo "ALTER TABLE $table ENGINE = MyISAM" | $MYSQLCMD $db
                fi
        done
done
{ 0 comments… add one }

Leave a Comment

*