How to know storage engine used of a database?
48,297
Solution 1
You can check per-table like this:
USE <database>;
SHOW TABLE STATUS\G
you'll get an output along these lines:
root@localhost/database> show table status\G
*************************** 1. row ***************************
Name: tablename
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 101
Avg_row_length: 70
Data_length: 7070
Max_data_length: 19703248369745919
Index_length: 2048
Data_free: 0
Auto_increment: 1004
Create_time: 2009-12-07 20:15:53
Update_time: 2010-11-10 21:55:01
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Solution 2
This query lists all InnoDB tables and their databases in MySQL:
SELECT table_name, table_schema
FROM information_schema.tables
WHERE engine = 'InnoDB';
You can also list all tables and their storage engines:
SELECT table_name, table_schema, engine
FROM information_schema.tables;
Solution 3
Use the 'show engine' command to view the active default engine
Add default-storage-engine=InnoDB in [mysqld] section of the my.cnf file for the default engine to be active.
Use the 'show create table table_name' command to view default engine in the table.
Solution 4
To get engine name for a specific table
use <database_name>
show table status like '<table_name>';
To change engine
alter table <table_name> engine <engine_name>;
Related videos on Youtube
Author by
J_Kay
Updated on September 17, 2022Comments
-
J_Kay over 1 year
Previously, on every database created, I use:
mysql -u root -p CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin; GRANT ALL ON dbname.* TO 'dbuser'@'localhost';
and then use the database without thinking about MyISAM or InnoDB
How to know storage engine used of a database?
-
voretaq7 about 13 yearsany way you know of to do all tables in a DB (besides writing a script to
SHOW TABLE
for each table in the DB)? -
voretaq7 about 13 yearscool -- I'm a postgres guy so all this MySQL and multiple storage engines stuff is strange to me :-)
-
mstrap almost 7 years
SHOW ENGINE
gives me ERROR 1064 (42000). -
w00t over 6 years
show engines;
-
nnyby almost 6 yearsI tried the second query - I get the error:
ERROR 1146 (42S02): Table 'information_scheme.tables' doesn't exist
(on MySQL 5.6.37)