MySQL Vs Cassandra

Last updated: October 19, 2026Oct 19, 2026 4 min read

MySQL is a relational database built around tables, joins and ACID transactions. Apache Cassandra is a distributed wide-column NoSQL database built for massive write volumes and always-on availability across data centres. They solve different problems, and many large companies run both.

1. Overview

2. Architecture

• MySQL writes go to one source server (or one primary in Group Replication). Replicas copy the changes and serve reads. If the source fails, a replica is promoted.
• Cassandra has no leader. A row's partition key is hashed to decide which nodes store it; the replication factor says how many copies. Any node can coordinate a request, so there is no single point of failure.
• Cassandra writes go to a commit log and an in-memory table, later flushed to immutable SSTable files (an LSM-tree design), which makes writes very fast.

3. Data Modelling: Normalise vs Model per Query

In MySQL you design tables around the data and normalise it, then write any query you like. In Cassandra you design one table per query, choosing the partition key so each query reads a single partition. Data is often duplicated across tables on purpose.

Example: show the latest temperature readings for a sensor. MySQL:

CREATE DATABASE iot;
USE iot;
CREATE TABLE readings (
  sensor_id  VARCHAR(10),
  read_at    DATETIME,
  temp_c     DECIMAL(4,1),
  PRIMARY KEY (sensor_id, read_at)
);
INSERT INTO readings VALUES
('S-101','2026-03-01 10:00:00',21.4),('S-101','2026-03-01 10:05:00',21.9),
('S-101','2026-03-01 10:10:00',22.6),('S-202','2026-03-01 10:00:00',18.2),
('S-202','2026-03-01 10:05:00',18.0);

SELECT read_at, temp_c
FROM readings
WHERE sensor_id = 'S-101'
ORDER BY read_at DESC
LIMIT 2;

Result: 2 rows

Cassandra (CQL), designed for exactly that query: sensor_id is the partition key and read_at is a clustering column stored newest first:

CREATE TABLE readings_by_sensor (
  sensor_id text,
  read_at   timestamp,
  temp_c    decimal,
  PRIMARY KEY ((sensor_id), read_at)
) WITH CLUSTERING ORDER BY (read_at DESC);

SELECT read_at, temp_c FROM readings_by_sensor
WHERE sensor_id = 'S-101' LIMIT 2;

The CQL looks like SQL, but the rules are stricter. The ad-hoc query below is easy in MySQL. In Cassandra, filtering on the non-key column temp_c across all sensors needs a secondary index or ALLOW FILTERING (a full scan of every node), and joins are not available at all:

SELECT sensor_id, MAX(temp_c) AS max_temp
FROM readings
WHERE temp_c > 20
GROUP BY sensor_id;

Result: 1 row

4. Consistency and Transactions

5. Performance Profile

• Writes: Cassandra handles very high write rates spread over many nodes; MySQL is bounded by the source server.
• Reads by key: both are fast. Cassandra reads one partition; MySQL uses its B-tree indexes.
• Ad-hoc queries, joins, reports: MySQL is far better; Cassandra only answers the queries its tables were designed for.
• Scaling: add a node to a Cassandra ring and data rebalances; growing MySQL writes usually means a bigger server or sharding.

6. When to Use Which

7. Summary

• MySQL is a relational, SQL, ACID database; Cassandra is a distributed wide-column database with CQL.
• MySQL has a source server and replicas; Cassandra is a masterless ring where any node accepts writes.
• Model MySQL tables around data (normalise); model Cassandra tables around queries (denormalise).
• Cassandra trades joins and transactions for massive write scale and availability with tunable consistency.