mysql_storageengine []

Storage Engines - InnoDB/myISAM

mysql > 5.5 the default is INNODB
mysql < 5.5 the default engine is MyISAM

Before we convert tables we can first view the table engine with:
Note: Change 'db_name' to your database

        
              SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'db_engine';

Now we have viewed the table engine we can go ahead and convert the tables.

Example: Changing MyISAM tables to InnoDB

Note: Change 'db_name' to the database you wish to change the engine of

        
              SET @DATABASE_NAME = 'db_name';
 
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

An example of this being run is:

        
          
          
              
              mysql> SET @DATABASE_NAME = 'table_engine';
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
mysql> SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
    -> FROM    information_schema.tables AS tb
    -> WHERE   table_schema = @DATABASE_NAME
    -> AND     `ENGINE` = 'MyISAM'
    -> AND     `TABLE_TYPE` = 'BASE TABLE'
    -> ORDER BY table_name DESC;
+-----------------------------------------------------+
| sql_statements                                      |
+-----------------------------------------------------+
| ALTER TABLE `wp_users` ENGINE=InnoDB;               |
| ALTER TABLE `wp_usermeta` ENGINE=InnoDB;            |
| ALTER TABLE `wp_term_taxonomy` ENGINE=InnoDB;       |
| ALTER TABLE `wp_term_relationships` ENGINE=InnoDB;  |
| ALTER TABLE `wp_terms` ENGINE=InnoDB;               |
| ALTER TABLE `wp_posts` ENGINE=InnoDB;               |
| ALTER TABLE `wp_postmeta` ENGINE=InnoDB;            |
| ALTER TABLE `wp_popularpostssummary` ENGINE=InnoDB; |
| ALTER TABLE `wp_popularpostsdata` ENGINE=InnoDB;    |
| ALTER TABLE `wp_options` ENGINE=InnoDB;             |
| ALTER TABLE `wp_links` ENGINE=InnoDB;               |
| ALTER TABLE `wp_comments` ENGINE=InnoDB;            |
+-----------------------------------------------------+
12 rows in set (0.00 sec)

            

        
      