Differences

This shows you the differences between two versions of the page.

--- mysql_storageengine [2015/10/27 09:32] – created luke7858
+++ mysql_storageengine [2024/05/23 07:26] (current) – external edit 127.0.0.1
@@ Line 3: / Line 3: @@
 \\
 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
+<sxh bash>
+SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'db_engine';
+</sxh>
+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
+<sxh bash>
+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;
+</sxh>
+An example of this being run is:
+<sxh bash>
+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;            |
++-----------------------------------------------------+
+rows in set (0.00 sec)
+</sxh>