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)

Arjun Thakur

Published on 22-Oct-2018 09:26:30

• Related Questions & Answers
• Converting table from MyISAM to INNODB in MySQL?
• MySQL - changing table engine from innoDB to MyISAM?
• When to use MyISAM and InnoDB?
• How to convert MyISAM to InnoDB storage engine in MySQL?
• 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?
• MySQL data types int versus enum?
• Resolve the MySQL error 'TYPE=MyISAM'?
• How can I see global locks in MySQL (innodb)?
• How can I install or enable innoDB in MySQL?
• Java String.equals() versus ==.
• ADSL versus Cable
• Using “TYPE = InnoDB” in MySQL throws an exception?
• How to create a MySQL table with MyISAM engine table?