#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

MySQL Storage Engines: InnoDB vs MyISAM Compared

For any new table in MySQL 8, use InnoDB. It has been the default storage engine since MySQL 5.5, MySQL's own system tables use it, and it is the only common engine with transactions, foreign keys, row-level locking and automatic crash recovery. MyISAM still appears in old tutorials, legacy PHP applications and some college lab manuals, so it is worth knowing what it does differently, and the experiments below show those differences on a real MySQL 8.0 server rather than in theory.

What a storage engine is

MySQL separates the SQL layer (parsing, optimising) from the storage layer that actually writes rows to disk. Each table chooses an engine, and the engine decides how data is stored, locked and recovered. You can list what your server supports:

SELECT ENGINE, SUPPORT, TRANSACTIONS
FROM information_schema.ENGINES
WHERE ENGINE IN ('InnoDB','MyISAM','MEMORY','CSV','ARCHIVE')
ORDER BY ENGINE;
ENGINESUPPORTTRANSACTIONS
ARCHIVEYESNO
CSVYESNO
InnoDBDEFAULTYES
MEMORYYESNO
MyISAMYESNO

Side-by-side comparison

FeatureInnoDBMyISAM
Transactions (COMMIT / ROLLBACK)YesNo
Foreign keysEnforcedParsed but ignored
LockingRow-level, so many users can write at onceWhole table on every write
Crash recoveryAutomatic, using the redo logTables can be marked crashed; needs REPAIR TABLE
Primary key storageClustered: rows stored in primary-key orderHeap file plus separate indexes
SELECT COUNT(*) with no WHERECounts rows (can be slow on huge tables)Instant, row count is stored
Full-text searchYes (since MySQL 5.6)Yes
Typical use todayEverythingLegacy tables, some read-only archives

Experiment 1: ROLLBACK on both engines

CREATE TABLE acc_innodb (id INT PRIMARY KEY, balance INT) ENGINE=InnoDB;
CREATE TABLE acc_myisam (id INT PRIMARY KEY, balance INT) ENGINE=MyISAM;
INSERT INTO acc_innodb VALUES (1,1000);
INSERT INTO acc_myisam VALUES (1,1000);

START TRANSACTION;
UPDATE acc_innodb SET balance = 0 WHERE id = 1;
UPDATE acc_myisam SET balance = 0 WHERE id = 1;
ROLLBACK;
SHOW WARNINGS;
LevelCodeMessage
Warning1196Some non-transactional changed tables couldn't be rolled back
SELECT 'InnoDB' AS engine, balance FROM acc_innodb
UNION ALL
SELECT 'MyISAM', balance FROM acc_myisam;
enginebalance
InnoDB1000
MyISAM0

The InnoDB change was undone; the MyISAM change was written immediately and stays. In a payment or inventory system that difference is the whole story. See COMMIT and ROLLBACK in MySQL for transactions in detail.

Experiment 2: foreign keys

CREATE TABLE dept_m (dept_id INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE emp_m (emp_id INT PRIMARY KEY, dept_id INT,
                    FOREIGN KEY (dept_id) REFERENCES dept_m (dept_id)) ENGINE=MyISAM;
INSERT INTO emp_m VALUES (1, 99);      -- department 99 does not exist
SELECT * FROM emp_m;
emp_iddept_id
199

MyISAM accepted an employee in a department that does not exist, without any error. The same tables on InnoDB:

CREATE TABLE dept_i (dept_id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE emp_i (emp_id INT PRIMARY KEY, dept_id INT,
                    FOREIGN KEY (dept_id) REFERENCES dept_i (dept_id)) ENGINE=InnoDB;
INSERT INTO emp_i VALUES (1, 99);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`shop`.`emp_i`, CONSTRAINT `emp_i_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept_i` (`dept_id`))

InnoDB protects referential integrity; with MyISAM your application has to do it. Keys are explained in primary key vs foreign key.

Locking: why MyISAM struggles with many users

When one session updates a MyISAM table, the whole table is locked for writing, and other sessions wanting to write wait in line. On a busy web app (say, a ticket booking page during a sale) that queue becomes the bottleneck. InnoDB locks only the rows being changed and uses multi-version concurrency control, so readers do not block writers and two users updating different rows proceed in parallel.

Checking and converting engines

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;

ALTER TABLE acc_myisam ENGINE = InnoDB;    -- converts the table in place
SELECT @@default_storage_engine;            -- InnoDB

Converting rebuilds the table, so on a large table run it in a maintenance window and take a backup first. After converting, existing data that breaks a foreign key will stop you adding that constraint, so clean orphans first (an anti-join with LEFT JOIN ... IS NULL finds them; see MySQL joins). If you omit ENGINE= in CREATE TABLE, you get the default, InnoDB.

Is there still any reason to choose MyISAM?

The other engines have narrow uses: MEMORY for small throwaway lookup tables (data is lost on restart), ARCHIVE for compressed insert-only logs, CSV for exchanging files. How InnoDB keeps hot data in RAM is covered in MySQL caching and the buffer pool, and how engines fit into the server's design in MySQL architecture explained.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →