I already answered this in StackOverflow : https://stackoverflow.com/a/11636341/491757
If you want to defrag all your MyISAM tables, here is a shell script to do so...
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='MyISAM' AND table_schema NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GlobalMyISAMOptmizeTable.sql
less GlobalMyISAMOptmizeTable.sql
Once you trust the script visually, just run it
mysql ${MYSQL_CONN} < GlobalMyISAMOptmizeTable.sql
Give it a Try !!!
If you need concurrency of heavy UPDATEs and INSERTs, you will want InnoDB
If you need deadlock resolution, you will want InnoDB
If you want a storage engine that caches both data and indexes, you will want InnoDB
If you want to access multiple CPUs effective, you will want InnoDB (and tune it to do so)
Please refer to my past articles on InnoDB:
Feb 12, 2011
: How do you tune MySQL for a heavy InnoDB workload?
Apr 14, 2011
: What are the main differences between InnoDB and MyISAM?
Aug 04, 2011
: Optimizing InnoDB default settings
Oct 05, 2011
: Query runs a long time in some newer MySQL versions
Dec 19, 2011
: Which first: upgrade mysql version or convert storage engine?
Jan 09, 2012
: Any gotchas at all with converting from MyISAM to InnoDB?
Jan 27, 2012
: importing myisam 5.0 database into a 5.5 innodb server
Jan 18, 2012
: Insert-heavy InnoDB table won't use all my CPU
Jan 21, 2012
: Is InnoDB Engine up to speed against Memory Engine?
Jul 16, 2012
: decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
You said you heard that MySQL 5.5 is faster than MySQL 5.0. Out of the box, that's not true. YOU MUST TUNE MySQL 5.5 TO GET GREAT PERFORMANCE OUT OF IT !!! Here are my past articles on that subject:
You mentioned MyISAM being faster than InnoDB. If there are a lot of wide VARCHAR columns in the MyISAM with the default ROW_FORMAT=Dynamic (Default), that can degrade MyISAM performance. Saying one storage engine is faster than the other depends on the application, read/write I/O ratio, storage engine tuning.
See my posts around this subject
Addressing your issue of whether to convert now or later to InnoDB:
In the fifth link I provided ( Which first: upgrade mysql version or convert storage engine? ), I recommend converting first. However, in your particular case, I would recommend converting later. The reason is simple: You are moving the data to a different server. MyISAM tables are very easy to port. Doing an rsync or scp of /var/lib/mysql is more expedient (faster) that converting to InnoDB because the data will be slightly (sometimes significantly) big when existing as InnoDB.
There are two things you will need to do:
NEED #1
If you rysnc/scp /var/lib/mysql
you will overwrite /var/lib/mysql/mysql. Therefore, I propose you move your data like this:
NEED #2
You need a straightforward script to convert MyISAM to InnoDB. Please run the following on the MySQL 5.5 DB Server :
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql
When you are satisfied with the conversion script, login to mysql on MySQL 5.5 and run
mysql> source /root/ConvertMyISAMToInnoDB.sql
Give it a Try !!!
Best Answer
This is a known issue (not a bug) all the days of Storage Engine technology
Someone asked a similar question in a Percona Forum in November 2014: The table size between InnoDB and MyISAM engines differs in 7 times.
This may have something to do with the granularity of storage between MyISAM and InnoDB.
MyISAM
According to MyISAM Dynamic Data File Layout
InnoDB
The size of an InnoDB Page is 16K by default : See
innodb_page_size
COMPARISON
Since InnoDB stores in a fixed 16K "frame" whereas MyISAM stores in frames that can vary in size up 16777212 (16M - 4), one should always expect InnoDB tables to be larger due to page splits that are required to fit data into fixed 16K "frames".