Explain MySQL storage engines.

MySQL storage engines are the components that handle how data is stored, retrieved, and managed in a database. Each storage engine has its own way of managing tables, indexes, transactions, and locking mechanisms. You can think of a storage engine as the “backend” for your MySQL tables. MySQL supports multiple storage engines, and you can choose the one that best fits your use case.

Here’s a detailed explanation:

---

1. What is a Storage Engine?

A storage engine is the part of MySQL that actually stores and manages the data in tables. When you create a table, you can specify a storage engine using:

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

If you don’t specify an engine, MySQL uses the default storage engine (usually InnoDB in modern versions).

---

2. Common MySQL Storage Engines

a) InnoDB

• Default engine in MySQL 5.5+
• Supports: ACID transactions, foreign keys, row-level locking
• Pros:
  • Reliable for transactional workloads
  • Crash-safe (data is not lost on crash)
  • Good for high-concurrency applications
• Cons:
  • Slightly slower for read-heavy, simple queries than MyISAM

---

b) MyISAM

• Legacy engine, default in older MySQL versions
• Supports: Only table-level locking, no transactions
• Pros:
  • Fast for read-heavy applications
  • Simple and lightweight
• Cons:
  • No transactions or foreign keys
  • Table can get corrupted on crash

---

c) MEMORY (HEAP)

• Stores data in RAM, so extremely fast
• Pros:
  • Very fast for temporary tables or caching
• Cons:
  • Data is volatile; lost when MySQL restarts
  • Limited by RAM size

---

d) CSV

• Stores data in CSV files
• Pros:
  • Easy to import/export to other applications
• Cons:
  • No indexes, slow for large datasets
  • Only suitable for simple storage

---

e) ARCHIVE

• Optimized for storing large amounts of data that is rarely read
• Pros:
  • Compressed, reduces storage
• Cons:
  • No indexes (except auto-increment), read is slow
  • Cannot update or delete rows

---

f) NDB (Clustered)

• Used in MySQL Cluster
• Data is distributed across multiple nodes for high availability
• Pros:
  • High redundancy and scalability
  • Real-time access
• Cons:
  • Complex setup
  • Limited functionality compared to InnoDB

---

3. Comparison Table

Engine	Transactions	Foreign Keys	Locking	Best Use Case
InnoDB	Yes	Yes	Row-level	General-purpose, transactional apps
MyISAM	No	No	Table-level	Read-heavy apps, logging
MEMORY	No	No	Table-level	Temporary tables, caching
CSV	No	No	Table-level	Data import/export
ARCHIVE	No	No	Table-level	Historical/log data storage
NDB	Yes	No	Row-level	Distributed, high-availability apps

---

4. Key Points

1. Choice depends on use case:
   • Transactional apps → InnoDB
   • Analytics/read-heavy → MyISAM or ARCHIVE
   • Temp data → MEMORY
2. Mixing engines: Different tables in the same database can use different engines.
3. Engine features matter: ACID compliance, locking, crash recovery, indexing, and performance vary by engine.