Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I determine type of mysql database : whether it is InnoDB or MyISAM?

Tags:

types

sql

mysql

  • How can I determine type of mysql database : whether it is InnoDB or MyISAM ?
  • How can I convert MyISAM to InnoDB or vice-versa ?
like image 629
Rachel Avatar asked Oct 28 '09 15:10

Rachel


People also ask

How do I know if I have MySQL InnoDB or MyISAM?

Simply check the value of the Engine column in the returned dataset to know which engine the table is using. Show activity on this post. SELECT ENGINE FROM INFORMATION_SCHEMA. TABLES WHERE TABLE_NAME='your_table_name' AND TABLE_SCHEMA='your_database_name'; -- or use TABLE_SCHEMA=DATABASE() if you have a default one.

How do I know what MySQL engine I have?

To determine which engine a database table is currently using, type the following command at the mysql> prompt. Replace database with the name of the database that you want to check: Copy SELECT TABLE_NAME, ENGINE FROM information_schema. TABLES where TABLE_SCHEMA = 'database';

How do I know if InnoDB is enabled?

The easiest way to check whether the InnoDB engine is enabled is to log in to phpMyAdmin, click the SQL tab, type the following command in the box: show engines; and click Go to execute the query and see the available storage engines. Next to InnoDB engine, in the Support row you will see Yes if InnoDB is enabled.

Where can I find InnoDB in MySQL?

You can view a list of InnoDB INFORMATION_SCHEMA tables by issuing a SHOW TABLES statement on the INFORMATION_SCHEMA database: mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB%'; For table definitions, see Section 26.4, “INFORMATION_SCHEMA InnoDB Tables”.


2 Answers

To determine the storage engine being used by a table, you can use show table status. The Engine field in the results will show the database engine for the table. Alternately, you can select the engine field from information_schema.tables:

select engine  from   information_schema.tables  where  table_schema = 'schema_name'    and table_name = 'table_name'  

You can change between storage engines using alter table:

alter table the_table engine = InnoDB; 

Where, of course, you can specify any available storage engine.

like image 185
James McNellis Avatar answered Sep 20 '22 22:09

James McNellis


Select the database in question and run show table status;

like image 28
mr-euro Avatar answered Sep 22 '22 22:09

mr-euro