EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Head to Head Differences Tutorial Cassandra vs MySQL

Cassandra vs MySQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 24, 2023

Cassandra-vs-MySQL

Difference Between Cassandra vs MySQL

Cassandra vs MySQL in this Data is everything, and with a boom in the amount of data today, choosing the right database management system is all the more important for all businesses. In a world that Oracle and SQL Server dominated, there are endless solutions to choose from now. One of the main reasons is the innovation possible due to open source solutions. In this article, I will be talking about two open-source databases. One is Cassandra which has gained much momentum over the past few years. And the other MySQL, which is the most common and popular database.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Head-to-Head Comparison Between Cassandra vs MySQL(Infographics)

Below are the Top 6 differences between Cassandra and MySQL:

Cassandra-vs-MySQL-info

Key Differences Between Cassandra vs MySQL

This article will look at the key differences between these two databases, which will help you determine which database to use.

Type of database

Cassandra is a NoSQL database. The Apache Software Foundation developed it. It was released in 2008. NoSQL(Not only SQL) databases were developed in response to the shortcomings of relational database management systems and to meet the demands of modern software development. NoSQL databases support a wide range of data, including key-value, document, and graph formats. It can store both unstructured and semi-structured data. It follows a peer-to-peer architecture.

MySQL is an open-source Relational Database Management System developed by Oracle and released in 1995. Compared to Cassandra, it is quite an old database. SQL databases support related data stored among different tables. MySQL stores structured data(high degree of organization). Essentially the relational model consists of tables (relations) that can be interlinked by keys common to multiple tables. It follows a Master/Slave architecture.

1. Scalability

MySQL provides support for vertical scaling. Horizontal scaling is possible with the help of some other approaches like Master/Slave replication and sharding.

Cassandra was developed with support for both horizontal and vertical scaling. Horizontal scaling is possible due to the cluster node model. Data is partitioned among different nodes in a cluster.

2. Data Model

The main difference that can be seen between MySQL and Cassandra is how the data is modeled.

Let us take an example to show how there is a significant difference between data modeling in these two databases. Consider a portal where a user can comment on a post made by another user; If we wanted to store this information, it would be stored in different ways in these two databases.

In MySQL, we would have to make two tables with one to many relationships between them, like below. MySQL doesn’t allow unstructured data, e.g., a List or a Map, so we require one-to-many relationships among these tables.

To fetch data, you would typically use JOINS on these tables.

data model 1

In Cassandra, this information can be stored in one table; we can store the comments for each user in the form of a List which would be stored as one row. Here user_id is the Partition key (using which data would be partitioned among the different nodes), and created_time is the cluster key using which the data would be sorted in a particular node.

data model 2

3. Read Performance

Now that we have looked at how we can model data in both databases. Let us analyze how our read performance changes from one database to another.

In MySQL, the query to get the comments made by a user with id say ‘3’ will look something like this,

SELECT * from Users u, Comments c WHERE u.user_id=c.user_id and user_id=3;

First, finding a user would be of O(log(U)). This is because when you use indexing in MySQL, it stores the data in the form of a binary tree, so any time you search for comments made by a user, you are only going to traverse through half of the tree. As there is a one to many relationships between the two, this would now take C*O(log(C)) as we will have to find each comment_id for a particular user_id.

In Cassandra, surprisingly, this decreases to just O(1).

Our query will look something like this,

SELECT * from Users WHERE user_id=3;

As we discussed, we would only have to store one row in Cassandra for a user_id. It would just require one lookup. In the worst case, it can go up to O(log(U)).

4. Write Performance

In MySQL, every INSERT/UPDATE/DELETE operation necessitates the execution of a search if you want to update a record with a primary key already in the table.

  • It will first search for the row
  • Then update it

Cassandra, on the other hand, uses an append-only model. Insert and update have no fundamental difference. If we insert a row with the same primary key as an existing one, the row will be replaced. If you update a row and the primary key does not exist, Cassandra will create that row.

5. Transactions

As with any other RDBMS database, MySQL provides ACID transactions

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Whereas Cassandra has limitations when it comes to providing ACID transactions. To provide high read performance, you would see data duplication. One way to achieve consistency would be to tune Cassandra not to allow data duplication, but then it kills Cassandra’s trump- Availability. So for systems highly based on ACID transactions like banking systems choosing any NoSQL database is a NO.

Let us summarize the key differences we looked at between Cassandra and MySQL. In case you did not read through the article and want to get the gist of it.

Cassandra and MySQL Comparison Table

Let’s discuss the top comparison between Cassandra and MySQL:

Basis of Comparison Cassandra MySQL
Type of database NoSQL, peer-to-peer architecture. RDBS, Master/Slave.
Scalability Horizontally and Vertically Scalable. Vertically Scalable, Horizontal scaling is possible through Master/Slave replication or sharding.
Data Model JOINS discouraged with the query-driven model—Fetches data from one table per query. Multiple tables, Joins required to query data.

 

Read Performance O(1) Requires reading from multiple tables using JOIN, resulting in O(log(n)).
Write Performance The append model provides high writing performance. Writing requires a search first, which slows down writing performance.
Transaction ACID properties are not provided but can be tuned to support ACID properties. Provides ACID transactions.

 

Conclusion

This article taught us some key differences between Cassandra and MySQL. In Data Science, most businesses utilize Cassandra for write-heavy workloads, while MySQL is the preferred choice for all other types of workloads. Hopefully, this will give you the knowledge to choose the proper database according to your needs.

Recommended Articles

We hope that this EDUCBA information on “Cassandra vs MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Cassandra vs Redis
  2. MongoDB vs Postgres
  3. Cassandra vs Elasticsearch
  4. Cassandra vs Couchbase | Top Differences
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
37+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
CASSANDRA Course Bundle - 14 Courses in 1
 36+ Hours of HD Videos
14 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more