How does InnoDB differ from MyISAM?

InnoDB supports transactions, foreign keys and row locking and recovers after a crash. MyISAM locks the whole table, has no transactions and is obsolete.

What is the clustered index in InnoDB?

The table is stored as a B-tree by primary key. Secondary indexes hold the primary key value, so a long PK bloats every index.

How does replication work?

The primary writes changes to the binlog and replicas read and apply them. That gives read scaling and a standby.

Databases

MySQL

One of the most widespread relational databases, the foundation of many web projects and the LAMP stack.

Official site Repository

What it is

MySQL was created by the Swedish company MySQL AB in 1995. Oracle has owned it since 2010, and the Community Edition is distributed under the GPL. Commercial editions also exist.

The main table engine InnoDB supports ACID transactions, foreign keys and row-level locking. Replication (async, semi-sync, Group Replication) is well proven.

MySQL underlies WordPress, Drupal, Magento and many large services. Cloud variants: Amazon RDS and Aurora, Google Cloud SQL, Azure Database.

Use it when

PHP web apps and CMS (WordPress, Laravel).
Simple and medium OLTP loads with many reads.
You need a database with a large hosting ecosystem.

Avoid it when

You need advanced types and extensions (geodata, vectors, complex indexes): PostgreSQL.
You do not want an Oracle dependency: consider MariaDB.

Key concepts

InnoDB: The default transactional engine.
Clustered index: InnoDB data is physically ordered by the primary key.
Binlog / реплікація: The binary change log for replicas and recovery.
Isolation levels: REPEATABLE READ by default.

Example

An InnoDB table and a limited select.

Cheat sheet

MySQL cheat sheet
Command or code	What it does
`mysql -u root -p`	Connect to the server
`SHOW DATABASES;`	List databases
`USE shop;`	Select a database
`SHOW TABLES;`	List tables
`DESCRIBE users;`	Table structure
`CREATE INDEX idx_email ON users (email);`	Create an index
`EXPLAIN SELECT * FROM users WHERE email = '[email protected]';`	Query execution plan
`SHOW INDEX FROM users;`	Which indexes a table has
`mysqldump -u root -p shop > shop.sql`	Back up a database to a SQL file
`mysql -u root -p shop < shop.sql`	Restore a database from a SQL file

How to learn it

Run MySQL in Docker and connect with the mysql CLI or DBeaver: create a database and a table.
Learn SQL: SELECT, JOIN, GROUP BY, subqueries. The SQL course on ShortLesson covers the basics.
Understand the InnoDB engine: transactions, foreign keys, row locking, the clustered primary key.
Pick the right types and charset (utf8mb4), design a schema and ship changes through migrations.
Add indexes, read EXPLAIN and fix a slow query that does a full scan.
Set up a separate least privilege user and regular backups with mysqldump.
Try replication (source and replica) and learn why reads from a replica can lag.
Connect MySQL to an app through an ORM (Prisma, Eloquent, Hibernate) and compare with raw SQL.

Interview questions

1. How does InnoDB differ from MyISAM?
InnoDB supports transactions, foreign keys and row locking and recovers after a crash. MyISAM locks the whole table, has no transactions and is obsolete.
2. What is the clustered index in InnoDB?
The table is stored as a B-tree by primary key. Secondary indexes hold the primary key value, so a long PK bloats every index.
3. How does replication work?
The primary writes changes to the binlog and replicas read and apply them. That gives read scaling and a standby.

More questions: SQL

Interview questions from the bank

Comparisons with this technology

Logos belong to their owners and are used for identification only. Icons: Simple Icons (CC0), Devicon (MIT).