MySQL Deep Dive — InnoDB, Replication, Partitioning

MySQL — গভীরতর আলোচনা

Read: ~50 min Advanced 14 practice problems Live SQLite runner

1. The Engine Behind Half the Internet

MySQL is, by raw deployment count, the most-installed RDBMS on Earth. WordPress runs on MySQL, so do most LAMP-stack apps, Daraz's checkout, bKash's transaction logging, and (until recently) most of Facebook, YouTube, and Twitter. The fork, MariaDB, has the same ecosystem and most of the same syntax. If Postgres is the "academic favourite," MySQL is the "battle-tested workhorse" — its sharp edges are well-known and its operational tooling is superb.

MySQL পৃথিবীর সবচেয়ে বেশি deploy হওয়া relational database। WordPress, Daraz, bKash-এর অনেক service — সবই MySQL-এ। এই module-এ আমরা MySQL-এর storage engine, replication, partitioning এবং MySQL 8-এর আধুনিক feature-গুলো গভীরভাবে দেখব — এবং প্রতিটি জায়গায় Postgres-এর সাথে তুলনা করব।

We'll cover: storage engines (and why InnoDB won), InnoDB internals (clustered index, undo/redo logs, doublewrite buffer), binary log formats, replication topologies (async, semi-sync, group), read replicas with ProxySQL/Orchestrator, partitioning (RANGE / LIST / HASH), and finally MySQL 8's modern feature set that closed most of the historical gap with Postgres.

What runs in this page Our in-browser runner is SQLite. Most "general" SQL runs on both MySQL and SQLite identically, so we'll use SQLite for runnable demos. MySQL-specific commands (SHOW ENGINES, CREATE TABLE … PARTITION BY, SHOW BINARY LOGS, replication setup) are shown as mysql-cli.sql snippets without Run buttons.

2. Storage Engines — InnoDB, MyISAM, MEMORY

Unique among major RDBMSes, MySQL lets each table choose its own storage engine — a pluggable layer that decides on-disk format, locking granularity, and crash recovery. Three matter:

Engine	Era / status	Properties	Use today
InnoDB	Default since 5.5 (2010).	Full ACID, row-level locks, MVCC, foreign keys, crash safe.	~99% of all new tables.
MyISAM	Pre-2010 default.	Table-level locks, no transactions, no FK, fast count(*).	Legacy schemas, archives. Avoid for new code.
MEMORY (HEAP)	Niche.	Entire table in RAM, hash index, vanishes on restart.	Very small lookup tables, intermediate temp results.
NDB	MySQL Cluster.	Distributed, shared-nothing, real-time.	Telco-grade availability, very specialised.
ARCHIVE	Niche.	Compressed, append-only.	Audit logs you rarely query.

মূল কথা: আজকের দিনে নতুন project-এ InnoDB ছাড়া অন্য কোনো engine বেছে নেওয়ার কারণ প্রায় নেই। MyISAM আছে শুধু পুরোনো schema-র সাথে compatibility-র জন্য। MEMORY engine ছোট lookup table-এর জন্য কাজে আসে, কিন্তু restart হলে data হারিয়ে যায় — সাবধান।

mysql-cli.sql

-- See available engines
SHOW ENGINES;

-- Pick an engine per table (InnoDB is default; explicit for clarity)
CREATE TABLE orders (
    id        BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id   BIGINT UNSIGNED NOT NULL,
    amount    DECIMAL(12,2)   NOT NULL,
    placed_at DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    KEY idx_user_placed (user_id, placed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Postgres comparison Postgres has one storage engine. That sounds limiting but it actually frees the project — every feature (MVCC, JSONB, FTS) plays well with everything else. MySQL's pluggable engines made it flexible early on but also caused real bugs (e.g., MyISAM's lack of transactions silently bypassing your COMMIT). Today, MySQL's pluggability is mostly historical — InnoDB is everything.

3. Inside InnoDB — Clustered Index, Logs, Buffers

3.1 The clustered index — "the PK is the row"

In InnoDB, every table is physically stored as a B+ tree keyed by the primary key, with the full row data living in the leaves. There is no separate "table heap" + "PK index" — the primary key index is the table.

InnoDB-এ primary key = পুরো table-এর physical order। Row-গুলো PK অনুসারে B+ tree-এর leaf-এ সাজানো থাকে। Postgres-এ এটি ঐচ্ছিক (CLUSTER চালালে এক বার সাজায়); MySQL-এ এটি স্থায়ী ও স্বয়ংক্রিয়। তাই PK পছন্দ করার সময় খুব ভেবে-চিন্তে বাছতে হয় — random UUID v4 PK রাখলে insert-এর সময় গাছ ঘন ঘন rebalance হবে।

Figure 42.1 — InnoDB-এ secondary index প্রথমে PK পায়, তারপর clustered tree-তে গিয়ে row আনতে হয়।

Consequence: every secondary index entry stores (secondary_key, primary_key). A query that reads non-indexed columns must do a second B+ tree walk to fetch the row from the clustered index. This is why covering indexes (indexes that contain every column the query needs) matter so much in MySQL.

3.2 Undo log, redo log, and the doublewrite buffer

Component	Purpose	Failure scenario it solves
Redo log (`ib_logfile*`)	Append-only physical change log.	Crash → replay redo on restart → no committed data lost.
Undo log	Per-transaction "how to roll back" log; also the source of MVCC old versions.	`ROLLBACK`, and consistent reads under MVCC.
Doublewrite buffer	InnoDB writes each 16 KB page first to a doublewrite area, then to its real location.	Torn-page on power loss (only half the 16 KB hit disk).
Buffer pool	RAM cache of data + index pages. Tunable via `innodb_buffer_pool_size`.	Disk reads avoided; usually sized to ~70% of server RAM.

Doublewrite buffer খুব মূল্যবান একটি detail — Linux-এর page size 4 KB, কিন্তু InnoDB-এর page 16 KB। মাঝপথে power চলে গেলে ৪ KB হয়তো লেখা হয়েছে, বাকি ১২ KB হয়নি — এটিকে বলে "torn page"। InnoDB তাই আগে একটি আলাদা doublewrite area-তে পুরো 16 KB লেখে, তারপর আসল জায়গায়। ক্র্যাশ হলে কোনটা অসম্পূর্ণ ছিল সেটি সেখান থেকে recover করা যায়। Postgres একই সমস্যা সমাধান করে "full page writes"-এর মাধ্যমে WAL-এ।

4. Binary Log & Replication

4.1 The binary log (binlog)

The binlog is a logical log of every change that modified data, written by the server independently of any storage engine. It powers point-in-time recovery and all replication. Three formats exist:

STATEMENT — logs the SQL itself. Compact but unsafe with non-deterministic functions (NOW(), UUID()).
ROW — logs the row image before/after. Always correct; usually larger. Default since MySQL 5.7.
MIXED — STATEMENT by default, ROW for unsafe statements. Reasonable middle ground.

mysql-cli.sql

SHOW VARIABLES LIKE 'binlog_format';
-- typically: ROW

-- List binlog files
SHOW BINARY LOGS;

-- Inspect the contents of one
SHOW BINLOG EVENTS IN 'binlog.000017' LIMIT 50;

-- Or from the shell:
-- mysqlbinlog --base64-output=decode-rows -v binlog.000017

4.2 Replication topologies

MySQL's replication has evolved from a single-leader log-shipping model into a small family of topologies:

Topology	Guarantee	Trade-off
Async leader → follower	Follower eventually catches up.	Lag possible; data loss window if leader dies before follower receives.
Semi-sync	Leader waits for at least one follower to ACK before COMMIT returns.	Higher write latency; durability across machines.
Group Replication / InnoDB Cluster	Multi-primary or single-primary with automatic failover; Paxos-style consensus.	More moving parts; needs MySQL Router / ProxySQL in front.
Galera (MariaDB Cluster, Percona XtraDB)	Synchronous multi-master with certification.	Slowest write commit, but every node has the data.

Async — সবচেয়ে সাধারণ; দ্রুত লেখা যায়, কিন্তু leader হঠাৎ মারা গেলে সর্বশেষ কয়েকটি transaction follower-এ পৌঁছায়নি — কিছু data হারাতে পারে। Semi-sync — অন্তত একটি follower ACK না দিলে COMMIT ফিরে আসে না; তাই data loss-এর সম্ভাবনা প্রায় শূন্য, কিন্তু write latency কিছুটা বাড়ে। Group Replication — Paxos-ভিত্তিক স্বয়ংক্রিয় failover; production-grade HA-এর জন্য MySQL 8-এর সুপারিশকৃত পথ।

Figure 42.2 — Production-style MySQL fleet: ProxySQL routes writes to leader, reads to replicas.

4.3 Read replicas + failover

Pure MySQL doesn't ship with automatic failover. Two operational tools dominate:

ProxySQL — a transparent SQL-aware proxy. App connects to ProxySQL; rules route writes to leader, reads to replicas. Supports query caching, connection pooling, online reconfiguration.
Orchestrator — a topology manager. Detects leader failure, promotes the most up-to-date replica, rewires the rest. Often paired with ProxySQL.
MySQL Router + InnoDB Cluster — Oracle's official answer; uses Group Replication underneath.

5. Partitioning — RANGE, LIST, HASH

Partitioning splits one logical table into many physical sub-tables ("partitions"), based on a partitioning key. Queries that filter on the key only scan relevant partitions ("partition pruning"); admin tasks like DROP PARTITION become instant.

একটি 50 GB-র orders table বছর-ভিত্তিক partition-এ ভাগ করলে ২০২৫-এর কোয়েরি কেবল ২০২৫-এর partition-ই scan করবে — বাকি ১০-১৫ বছরের data ছোঁবেই না। আবার ১০ বছর পুরোনো order সরাতে চাইলে DROP PARTITION p_2015 মুহূর্তেই কাজ সারে — DELETE-এর তুলনায় হাজার গুণ দ্রুত।

Type	Key shape	Best for
`PARTITION BY RANGE`	Continuous, ordered (date, id).	Time-series — orders, logs, telemetry.
`PARTITION BY LIST`	Discrete category (country, region).	Per-region tenancy.
`PARTITION BY HASH`	Hash of a column.	Even spread when no natural range; reduces hot spots.
`PARTITION BY KEY`	Like HASH but uses MySQL's built-in hash on PK.	Default-ish HASH.

mysql-cli.sql

-- Time-series order log, RANGE-partitioned by year
CREATE TABLE orders_log (
    id          BIGINT UNSIGNED NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    amount      DECIMAL(12,2)   NOT NULL,
    placed_at   DATETIME        NOT NULL,
    PRIMARY KEY (id, placed_at)         -- partition key MUST be in PK
)
PARTITION BY RANGE (YEAR(placed_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- Drop a whole year in milliseconds
ALTER TABLE orders_log DROP PARTITION p2023;

-- Inspect pruning
EXPLAIN PARTITIONS
SELECT SUM(amount) FROM orders_log
WHERE placed_at >= '2025-01-01' AND placed_at < '2025-02-01';
-- partitions column should show only p2025

The "partition key in primary key" trap MySQL requires every unique index — including the PK — to contain the partition key. So a perfectly natural PRIMARY KEY (id) won't compile if you partition by placed_at. You must use a composite PK like (id, placed_at), which sometimes ripples out into foreign-key targets. Plan your PK and partition strategy together.

Postgres equivalent

Postgres has declarative partitioning since v10 with the same RANGE / LIST / HASH variants. Syntax differs (CREATE TABLE … PARTITION BY RANGE (placed_at), then CREATE TABLE p2025 PARTITION OF parent FOR VALUES FROM (...) TO (...)), but the concept is identical.

6. MySQL 8 — Closing the Gap with Postgres

For a decade, "use Postgres for serious SQL" was conventional wisdom because MySQL lacked CTEs, window functions, and lateral joins. MySQL 8 (2018) closed almost all of those gaps. Highlights:

Feature	What it gives you	Notes
CTEs (`WITH`) and `WITH RECURSIVE`	Readable named sub-queries; tree / graph traversal.	Same standard syntax as Postgres.
Window functions	`ROW_NUMBER()`, `RANK()`, `LAG()`, `SUM() OVER (...)`.	Identical to Postgres in 95% of cases.
JSON path & functions	`JSON_EXTRACT`, `->`, `->>`, `JSON_TABLE`.	No GIN equivalent; can index a generated column on the path.
Descending indexes	`INDEX (a ASC, b DESC)` — index actually stores DESC.	Speeds up `ORDER BY a, b DESC`.
Invisible indexes	Index exists but optimizer ignores it.	Test "does this index matter?" without dropping it.
Hash join	Optimizer picks hash join for big equi-joins instead of nested-loop.	Huge wins on warehouse-style queries.
Atomic DDL	`CREATE/DROP TABLE` are now transactional.	Crash mid-DDL no longer leaves orphans.
Roles	Group privileges into roles, grant the role.	Catches up to standard SQL roles.
UTF8MB4 default	Full Unicode incl. emoji and most Bangla.	Old `utf8` was 3-byte only and broke 4-byte chars.

6.1 A MySQL 8 query in our SQLite runner

CTEs and window functions are standard; SQLite supports both, and so does MySQL 8 — so this query runs identically in either engine.

cte_window.sql

-- Per-city revenue, with each city's % of total — uses CTE + window.
WITH city_rev AS (
    SELECT city, SUM(amount) AS revenue
    FROM   orders
    GROUP BY city
)
SELECT city,
       revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total,
       RANK() OVER (ORDER BY revenue DESC)            AS city_rank
FROM city_rev
ORDER BY city_rank;

মনে রাখুন: CTE আর window function একই syntax-এ MySQL 8, Postgres, SQLite, SQL Server, Oracle — সব জায়গায় চলে। এটি SQL-এর portable অংশ। যা বদলায় সেটি বিক্রেতা-নির্দিষ্ট ছোট ছোট details (যেমন partitioning syntax বা date function-এর নাম)।

7. MySQL vs Postgres — When to Choose Which

✅ MySQL fits when (কখন MySQL)

You're on a LAMP / WordPress / Drupal stack — every plugin assumes MySQL.
Read-heavy web workload with simple OLTP queries.
You need mature, well-documented horizontal sharding (Vitess) or read replicas.
Operations team is most comfortable with MySQL tooling (Percona Toolkit, ProxySQL, pt-osc).
Managed services like RDS for MySQL or PlanetScale fit your budget.

✅ Postgres fits when (কখন Postgres)

You need rich types — JSONB, arrays, ranges, geo, vectors.
Complex analytical queries with CTEs, lateral joins, window-heavy reports.
You want extensions (PostGIS, pgvector, pg_stat_statements) bundled in.
Strict SQL standard compliance and richer constraint system (EXCLUDE, partial indexes).
Logical replication / CDC is part of the architecture.

Topic	MySQL 8	PostgreSQL 16
MVCC	Via undo log + InnoDB rollback segments.	Via on-page tuple versions + VACUUM.
Default isolation	`REPEATABLE READ`	`READ COMMITTED`
JSON	`JSON` type with `JSON_EXTRACT`; index via generated column.	`JSONB` with native `GIN` index.
Replication	Binlog-based; async / semi-sync / Group.	WAL-based physical; logical via publications.
Partitioning	RANGE / LIST / HASH; partition key must be in PK.	Declarative partitioning since v10; no PK constraint.
Procedural lang.	Stored procedures (`CREATE PROCEDURE`).	PL/pgSQL, plus PL/Python, PL/V8 etc.
Geo	Native `GEOMETRY` type, R-tree on InnoDB.	PostGIS extension — far richer.
Vendor	Oracle Corp (since 2010).	PostgreSQL Global Development Group (community).

Honest take For 80% of Bangladeshi web startups, both work. Pick MySQL if your team and hosting (cPanel, RDS) lean that way; pick Postgres if you anticipate JSONB/geo/vector or if you simply prefer stricter SQL. Don't agonize — modern MySQL and Postgres are far closer than blog wars suggest.

8. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
Storage engine	Pluggable layer deciding on-disk format and locking.	Disk format ও locking decision-এর pluggable layer।
Clustered index	Index whose leaves are the actual rows; PK in InnoDB.	যে index-এর leaf-ই row — InnoDB-এ PK।
Redo log	Append-only physical change log used for crash recovery.	Crash recovery-র জন্য append-only physical log।
Doublewrite buffer	InnoDB area to avoid torn-page on power loss.	Power loss-এ torn page এড়ানোর InnoDB area।
Binlog	Server-level logical change log used by replication and PITR.	Replication ও PITR-এর জন্য server-level change log।
Semi-sync replication	COMMIT waits for ≥1 follower ACK.	COMMIT-এর আগে অন্তত একটি follower-এর ACK-এর অপেক্ষা।
Partition pruning	Optimizer skips partitions that can't match the WHERE.	WHERE-এর সাথে মিলবে না এমন partition optimizer-ই বাদ দেয়।
Invisible index	Index that exists but optimizer ignores; for safe testing.	Index আছে, কিন্তু optimizer agnore করে — safely test করার জন্য।

9. Practice Problems

Mix of concept questions, MySQL syntax exercises, and SQLite-runnable demos for portable concepts.

কিছু prom পাড়াগাঁ-style theory, কিছু MySQL syntax লেখা, কিছু এই pages-এ চালানো যাবে।

Why is choosing a random UUID v4 as the InnoDB primary key bad for write performance?

UUID v4 PK কেন InnoDB-তে write-এ ক্ষতিকর?

✨ Show Answer

Answer: InnoDB stores rows ordered by PK in a B+ tree. Random UUIDs cause inserts to scatter across the tree, fragmenting pages and exploding the working set. A monotonically increasing PK (auto-increment, UUIDv7, ULID) keeps inserts at the rightmost leaf — far cheaper.
What problem does the doublewrite buffer solve?

Doublewrite buffer কোন সমস্যা সমাধান করে?

✨ Show Answer

Answer: Torn pages — when a 16 KB InnoDB page is half-written to disk because the OS page is only 4 KB and a power failure occurred mid-flush. Doublewrite first stages the full 16 KB in a separate area; on recovery, InnoDB compares and restores the bad page from the doublewrite copy.
Difference between binlog formats STATEMENT and ROW in one line each.

STATEMENT এবং ROW format-এর পার্থক্য?

✨ Show Answer

STATEMENT — logs the SQL itself (small, but unsafe for non-deterministic functions like NOW() / UUID()).
ROW — logs before/after row images (always correct, larger volume; the modern default).
In semi-sync replication, what specifically is the leader waiting for before COMMIT returns?

Semi-sync replication-এ leader ঠিক কিসের জন্য অপেক্ষা করে?

✨ Show Answer

Answer: An ACK from at least one follower confirming that follower has received (not necessarily applied) the binlog event. So if the leader dies right after, that transaction still exists on a follower and can be promoted without loss.
Run the CTE+window query from §6.1 — list cities with their revenue rank and % of total. (No setup needed besides the data block.)

§6.1-এর CTE + window query চালিয়ে output দেখুন।

✨ Show Answer

Just click Run in §6.1 above. You should see Dhaka with the largest share, then Chittagong, then Sylhet, each with rank 1, 2, 3 and percent-of-total adding up to 100.

Write the MySQL CREATE TABLE for an app_log table partitioned by month of created_at, with PK (id, created_at).

MySQL-এ মাস-ভিত্তিক partition-সহ app_log table-এর CREATE TABLE লিখুন।

✨ Show Answer

CREATE TABLE app_log (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    level       ENUM('INFO','WARN','ERROR') NOT NULL,
    msg         TEXT NOT NULL,
    created_at  DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p_2026_05 VALUES LESS THAN (TO_DAYS('2026-06-01')),
    PARTITION p_2026_06 VALUES LESS THAN (TO_DAYS('2026-07-01')),
    PARTITION pmax       VALUES LESS THAN MAXVALUE
);

Show the SQLite-runnable per-customer running total: cumulative SUM(amount) OVER (PARTITION BY customer ORDER BY placed_at).

প্রতিটি customer-এর জন্য তারিখ অনুসারে cumulative spend বের করুন।
✨ Show Answer
ans7.sql
```
SELECT customer,
       placed_at,
       amount,
       SUM(amount) OVER
         (PARTITION BY customer ORDER BY placed_at) AS running_total
FROM   orders
ORDER BY customer, placed_at;
```
When would you make an index invisible rather than dropping it?

Index drop না করে invisible কেন বানাবেন?

✨ Show Answer

Answer: When you suspect an index is unused but a wrong drop would force you to rebuild a multi-GB index under load. Marking it invisible makes the optimizer ignore it. If performance is fine for a week → drop it permanently. If something regresses → flip it visible again instantly.
Why is a covering index especially valuable in InnoDB compared to (say) Postgres?

Covering index InnoDB-তে কেন বিশেষভাবে মূল্যবান?

✨ Show Answer

Answer: Because every secondary index in InnoDB stores (secondary_key, primary_key) only — fetching extra columns means a second B+ tree lookup in the clustered index. A covering index includes the needed columns directly, skipping that lookup. Postgres heap rows are addressed by tuple-id, so the second lookup is a single page read instead of a tree walk — still beneficial there too, but the win is smaller.
A query in production has skyrocketed in latency after a schema change. The binlog is in ROW format. How would you reproduce the bad row update on a staging copy?

Production-এর একটি bad UPDATE staging-এ reproduce করতে চাইলে কী করবেন?

✨ Show Answer

Answer: Use mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000XYZ to decode the relevant binlog file into pseudo-SQL showing the actual before/after rows, then replay the suspect statement on staging. Combined with --start-datetime / --stop-datetime you can target the exact window.
Choose between RANGE, LIST, and HASH partitioning for: (a) a country-specific tenancy table; (b) a click-stream by day; (c) a user-shard for millions of accounts with no temporal access pattern.

তিনটি ক্ষেত্রে কোন partition strategy?

✨ Show Answer

(a) LIST by country code.
(b) RANGE by day (or month) of timestamp.
(c) HASH (or KEY) on user_id for even distribution.
Why does the partitioning key need to be part of the primary key in MySQL?

MySQL-এ partition key PK-তে থাকা কেন বাধ্যতামূলক?

✨ Show Answer

Answer: Because the PK uniqueness must be enforced per partition only. If the partition key were not in the PK, two rows with the same PK could end up in different partitions, defeating uniqueness. Postgres has the same logical requirement for unique constraints on partitioned tables.
Explain in one paragraph the journey of a single COMMIT under semi-sync replication.

Semi-sync replication-এ একটি COMMIT-এর ভ্রমণ এক অনুচ্ছেদে বলুন।

✨ Show Answer

Answer: The leader's InnoDB engine writes the redo log entry and flushes it to disk; simultaneously the server-level binlog records the change in ROW format. The binlog event is shipped to followers; at least one follower writes it to its relay log and ACKs back. Only after that ACK does the leader's COMMIT return to the client. If the leader crashes after redo flush but before binlog ship, recovery rolls forward redo and the binlog re-emits; if it dies after the ACK, the follower already has the data and can be promoted with no loss.
A startup picks "MySQL because WordPress." Two years in, they need geo queries (riders within X km), full-text search in Bangla, and similarity search on product embeddings. Should they migrate to Postgres?

পরিস্থিতি অনুযায়ী Postgres-এ migrate করবেন কি?

✨ Show Answer

Answer: The three new requirements all map onto Postgres extensions (PostGIS, Bangla FTS via tsvector / pg_trgm, pgvector). In MySQL they would each need a separate engine (Elasticsearch, a vector DB, hand-rolled geo). Migration cost is real but bounded; long-term, one Postgres beats four services. Yes — plan a logical-replication-based migration, port hot-path queries first, keep WordPress on MySQL if needed.

Summary — Module 42

MySQL is the most-deployed RDBMS, and InnoDB is its real engine. InnoDB stores every table as a B+ tree keyed by the primary key (the clustered index); secondary indexes store only the PK, so covering indexes pay for themselves. Crash safety comes from redo log + doublewrite; MVCC and rollback come from the undo log. The binlog (in ROW format) powers replication, point-in-time recovery, and CDC. Pick a topology — async, semi-sync, or Group Replication — based on how much data loss you tolerate. Partitioning by RANGE / LIST / HASH lets you scale and prune time-series and per-region tables. MySQL 8 brings CTEs, window functions, JSON, hash join, descending and invisible indexes, atomic DDL — closing most of the historical gap with Postgres.

MySQL = InnoDB + binlog। InnoDB-এ PK = পুরো table-এর physical order; secondary index শুধু PK store করে, তাই covering index অনেক কাজে আসে। Crash safety আসে redo log + doublewrite থেকে; MVCC ও rollback আসে undo log থেকে। Binlog (ROW format)-এর উপরই দাঁড়ানো replication এবং PITR। বড় table-এ time-series workload হলে partitioning ভাবুন। MySQL 8-এর CTE, window function, hash join, atomic DDL — আজ MySQL ও Postgres অনেক কাছাকাছি।

Next Module → Other RDBMS — SQLite, SQL Server, and Oracle. When you'll meet each.

Prev: PostgreSQL Next: Other RDBMS