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)
Published on 22-Oct-2018 12:56:30
