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?

asked Jul 31, 2013 by EricaS. (210 points)

1 Answer

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

answered Jul 31, 2013 by anonymous
edited Jul 31, 2013

Please log in or register to answer this question.

Related questions

Copyright © SysTutorials. User contributions licensed under cc-wiki with attribution required.
Hosted on Dreamhost

...