How to find out and change the storage engine of tables in MySQL

How to find out and change the storage engine of tables in MySQL databases?

Find out the storage engine of a table in a database:

SELECT ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database'
AND TABLE_NAME = 'table'

Change the storage engine of a table:

ALTER TABLE table ENGINE = type

type can be innodb or myisam or others supported.

Set the default storage engine for the MySQL server:

set

default-storage-engine=type

in /etc/my.cnf.

More about MySQL configuration: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_default-storage-engine

Leave a Reply

Your email address will not be published. Required fields are marked *