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

Previous Page Print Page