
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
MyISAM versus InnoDB in MySQL?
Both are engine types. Here is the query by which we can get to know which engine type and tables are being used. Firstly, we will choose the database with the help of USE command −
mysql> USE business; Database changed
Here is the query through which we can know which table or engine is being used −
mysql> SHOW table status;
The following is the output
+------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ |Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |Max_data_length | Index_length | Data_free | Auto_increment | Create_time |Update_time | Check_time | Collation | Checksum | Create_options |Comment | +------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | addcolumntable | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL |2018-09-27 13:14:31 | NULL |NULL |utf8mb4_0900_ai_ci |NULL | | | | autoincrement | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | 4 |2018-10-06 13:28:25 | NULL |NULL |utf8mb4_unicode_ci | NULL | | | | autoincrementtable | InnoDB | 10 | Dynamic | 5 | 3276 | 16384 | 0 | 0 | 0 | 1001 |2018-10-04 09:39:47 | NULL | NULL |utf8mb4_unicode_ci | NULL | | | | bookindexes | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 |16384 | 0 | NULL | 2018-10-01 16:28:10 | NULL | NULL |utf8mb4_unicode_ci | NULL | | | | chardemo | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 |0 | 0 | NULL | 2018-09-28 11:00:28 | NULL | NULL | utf8mb4_0900_ai_ci |NULL | | | | clonestudent | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 |32768 | 0 | NULL | 2018-10-10 09:36:03 | NULL | NULL |utf8mb4_unicode_ci | NULL | | | | columnvaluenulldemo | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 |0 | 0 | NULL | 2018-10-05 12:06:27 | NULL | NULL |utf8mb4_unicode_ci | NULL | | | | dateadddemo | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 |0 | 0 | NULL | 2018-10-08 09:47:46 | NULL | NULL | utf8mb4_unicode_ci | NULL | | | | deletedemo | InnoDB | 10 | Dynamic | 5 | 3276 | 16384 | 0 |0 | 0 | NULL | 2018-10-09 17:18:38 | NULL | NULL | utf8mb4_unicode_ci | NULL | | | | deleterecord | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 |
So, in MySQL version 8.0.12, tables are using engine “innodb”, but we can change the engine type to MyISAM.
Here is the query to change −
mysql> SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;') -> from Information_schema.TABLES WHERE TABLE_SCHEMA = 'sample' AND ENGINE -> = 'InnoDB' AND TABLE_TYPE = 'mytable'; Empty set (0.05 sec)
- Related Articles
- Converting table from MyISAM to INNODB in MySQL?
- MySQL - changing table engine from innoDB to MyISAM?
- How to convert MyISAM to InnoDB storage engine in MySQL?
- When to use MyISAM and InnoDB?
- Can I use InnoDB and MyISAM tables in a single database in MySQL?
- How do I know if a MySQL table is using myISAM or InnoDB Engine?
- Is INNODB enabled by default in MySQL?
- Using “TYPE = InnoDB” in MySQL throws an exception?
- MySQL data types int versus enum?
- How can I see global locks in MySQL (innodb)?
- How can I install or enable innoDB in MySQL?
- Resolve the MySQL error 'TYPE=MyISAM'?
- How to display all tables in MySQL with InnoDB storage engine?
- How to create a MySQL table with MyISAM engine table?
- How to create a MySQL table with InnoDB engine table?

Advertisements