Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
192 Views
Advertisements