Posts Tagged "innodb"

InnoDB is red hot, MyISAM not

Planet MySQL

via InnoDB is red hot, MyISAM not.

Everyone who started using MySQL before 5.5 started off with MyIsam. It was the default storage engine and you had to go out of your way to use anything else. It was a good looking database, rugged…

MySQL Query to get Data, Index and Table Size by Storage Engines

SELECT IFNULL(B.engine,’Total’) “Storage Engine”, CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),’,’,”),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B’) “Data Size”, CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),’,’,”),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B’) “Index Size”, CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),’,’,”),17,’ ‘),’ ‘,SUBSTR(‘ KMGTP’,pw+1,1),’B’) “Table Size” FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN (‘mysql’,’information_schema’) AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

Sample Report:

MyISAM 0.031 GB 0.013 GB 0.044 GB
InnoDB 0.599 GB 0.471 GB 1.069 GB
Total 0.630 GB 0.484 GB 1.113 GB