If you are into web development then you no doubt work with databases a lot. Most of us create databases in cPanel without even thinking about what we need to use it for. As a result, we hardly give any thought to which database engine (or storage engine) to use for our database. A database engine is an underlying software that the DBMS uses to create, update, read or delete data from the MySQL database. The MySQL database management system (DBMS) has 14 different database engines that act as handlers for different table types.
MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables. We are going to focus on the two most popular ones for our purposes:
MyISAM
MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as full-text searching capabilities. MyISAM is supported in all MySQL configurations and is the default storage engine unless you have configured MySQL to use a different one by default. Uses table-level locking, so only one write per table can be done at a time.
Features:
- MYISAM supports Table-level Locking
- MyISAM designed for need of speed
- MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
- MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
- MYISAM does not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
- MYISAM supports full-text search
- You can use MyISAM, if the table is more static with lots of select and less update and delete.
INNODB
The InnoDB storage engines provide transaction-safe tables. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. Supports row-level locking, which means that multiple writes can be supported per table at the same time. This is also referred to as a transactional database engine.
Features:
- InnoDB supports Row-level Locking
- InnoDB designed for maximum performance when processing high volume of data
- InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
- InnoDB stores its tables and indexes in a tablespace
- InnoDB supports transaction. You can commit and rollback with InnoDB
| My ISAM | InnoDB | |
| Required full-text Search | Yes | |
| Require Transactions | Yes | |
| Frequent ‘Select’ queries | Yes | |
| Frequent ‘Insert, Update, Delete’ queries? | Yes | |
| Row Locking (multiprocessing on single table) | Yes |