Storage Engines in MySQL

-- Check storage engine
SHOW TABLE STATUS WHERE Name = 'users';
SHOW ENGINES;

-- Create with specific engine
CREATE TABLE transactions (
  id INT PRIMARY KEY,
  amount DECIMAL(10,2)
) ENGINE=InnoDB;  -- Default, supports transactions

CREATE TABLE logs (
  id INT PRIMARY KEY,
  message TEXT
) ENGINE=MyISAM;  -- Fast, no transactions

-- Change storage engine
ALTER TABLE users ENGINE=InnoDB;

-- DETAILED_TAB:
-- InnoDB features (default)
• Transactions (ACID compliant)
• Foreign keys
• Row-level locking
• Crash recovery
• Better for write-heavy

-- MyISAM features
• Table-level locking only
• No transactions
• Smaller disk footprint
• Better for read-heavy
• Full-text indexing (older MySQL)

-- Memory engine
CREATE TABLE cache (
  key_name VARCHAR(255) PRIMARY KEY,
  value TEXT
) ENGINE=MEMORY;  -- RAM storage, lost on restart

-- Convert all tables to InnoDB
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema = 'your_db';