Can I use InnoDB and MyISAM tables in a single database in MySQL?

Yes, you can use InnoDB and MyISAM tables in one database or combine both of them in a single database. This is the recommended way.

Here is the demo of both MyISAM and InnoDB in a one database. The following is the database and both the table types InnoDB and MyISAM. The query to create a database is as follows −

mysql> create database BothInnoDBandMyISAM;
Query OK, 1 row affected (0.20 sec)
mysql> use BothInnoDBandMyISAM;
Database changed

I have a database with name ‘BothInnoDBandMyISAM’.

First the table has engine type InnoDB. The query to create a table with engine InnoDB is as follows −

mysql> create table Student
   -> (
   -> StudentId int,
   -> StudentName varchar(20)
   -> )ENGINE=InnoDB;
Query OK, 0 rows affected (1.20 sec)

The second table has engine type MyISAM. The query to create a table with engine MyISAM is as follows −

mysql> create table Employee
   -> (
   -> EmployeeId int,
   -> EmployeeName varchar(30)
   -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.28 sec)

Here is the query to check the engine type from the same database. The query is as follows for the first table.

mysql> select engine from information_schema.tables where table_name='Student' and table_schema='BothInnoDBandMyISAM';

The following is the output −

+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)

Here is the query to check table engine type from the same database. The query is as follows for the second table.

mysql> select engine from information_schema.tables where table_name='Employee' and table_schema='BothInnoDBandMyISAM';

The following is the output −

+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)