Table of Contents
Storing data in relational models is not new. The RDBMS(Relational Database Management System) has been in vogue for decades, especially for web-based enterprise applications. The rapid increase in distributed and cloud computing, and changing expectations of data users in data storage and management, paved the way to the two most popular open source databases- MySQL and MongoDB.
Open source databases as you may already know, are “Free” source codes and are available for study, modification, and redistribution.
A comparison of the two databases as described in this article will help you make prudent decisions and choose the most appropriate database for your application.
What is MySQL?
MySQL is an open-source relational database management system, stores data in tables and maintains a relationship between the data. It uses the most powerful query language SQL (Structured Query Language) for database access and has a very powerful syntax to create simple and complex queries to retrieve and structured data. Data in MySQL is organized and conforms to a certain format, and hence it is the most popular structured database today.
Now, we are going to see a brief about NoSQL and Document Store – the precursors to understanding MongoDB.
NoSQL – Not only SQL – the main characteristics of a NoSQL database is that it does not store data in the traditional table format like in an RDBMS. NoSQL databases deal with gigantic volumes of data hence they follow a distributed database model. Document Databases, Graph databases, Key-value databases, and Wide column stores are the four primary classifications in the NoSQL family.
Document Store – Document Store as the name suggests stores the data as JSON-objects in documents whose structure does not have to be predefined. Data can be nestled hence the need to join data like in the relational databases is eliminated. Each document can be compared to a traditional row in a relational database, and each property in the JSON-object as a column.
Now, what is MongoDB all about?
What is MongoDB?
MongoDB is a non-relational unstructured database. This document-oriented database stores your data in collections made out of individual documents. In MongoDB, a document is a big JSON object with no particular format or schema. MongoDB represents JSON documents in a binary encoded format named as BSON.
Having got a broad understanding of MongoDB and MySQL, let us take a look at a few similarities and differences between the two powerful databases.
What is common?
Both MySQL and MongoDB are open source databases.
- They are built on common terminologies and concepts like maintaining ACID(Atomicity, Consistency, Isolation, and Durability) properties in transactions, storing, grouping, joining and managing data and having the secondary index.
- The two databases render service on all major cloud platforms.
- They also have a rich query language to access the data.
How do they differ? 6 Key Differences
Data Storage and Structure
As MySQL follows the relational model, data is stored in tables and you have to pre-define the schema based on the requirements along with rules that have to be set up between fields in your tables. Whereas, in MongoDB data is stored as documents in a collection. You need not to define the structure of the documents. If a new field is added to a document, the field can be created without affecting other documents in the collection. This key difference is a big advantage to developers as the code defines the schema and you do not have to go through schema migrations anymore.
Developing applications in MySQL is a lot slower as it uses the rigid table structure model. Working with data as flexible JSON documents in MongoDB has accelerated development cycles by 4 to 5 times. MongoDB documents map naturally to object-oriented programming languages, hence it is easy for developers to visualize how data in the application will map to data in the database.
In a MySQL database, data is spread across multiple tables, hence multiple tables need to be accessed to read and write data. Documents in MongoDB make applications fast as all the data for an entity is stored in a single document. This facilitates read and writes of data in a single place. Though MySQL has added support for JSON, it does not bring in the same productivity benefits.
MySQL supports atomic transactions, i.e.you can have several operations within a transaction. MySQL scored over MongoDB for this feature until MongoDB introduced the support for transactions. MongoDB 4.0 added support for multi-document transactions making it a powerful open source database in the unstructured space. Though there are few limitations for transactions as some operations are not supported in MongoDB, it is still a boon to the developer community.
MySQL is not built on a distributed system architecture, however,‘MySQL Cluster’ is the new distributed database in the MySQL family. MongoDB is built completely on a distributed architecture, as a result, MongoDB offers data localization with automatic sharding and replica sets to maintain ‘always-on’ availability. Hence, in MongoDB data becomes available globally but placed locally in specific geographies for governance and low latency access.
Native language drivers
As mentioned above, though MySQL comes with JSON support, developers are still tied up with multiple layers of SQL functionality to interact with JSON data. If you are a developer who wants to interact through APIs that are idiomatic to your programming language these layers are an overhead.
That is where MongoDB is positioned better than MySQL. MongoDB drivers and the APIs are native to the programming language of the developer.
What’s new in the two?
- MongoDB 4.0 is the latest evolution of MongoDB with added features like multi-document ACID transactions, data conversions and so on.
The MongoDB Stitch – the serverless platform, lets you execute any MongoDB query, right from inside your frontend app. Stitch ‘triggers’ let your app respond to real time data changes. You need not write and maintain lengthy stored procedures and triggers like in MySQL.
MongoDB Mobile also brings in the power of the document model to your mobile and IoT devices.
- MySQL 8.0 is the latest product in the MySQL family with enhanced features in JSON extended syntax, improved performance, with reliable DDL statements that are atomic and so on.
Whenever MongoDB is selected over MySQL, there have been radical improvements in productivity, performance and scalability. Here are few use cases as examples that depict these benefits.
- Viacom Media Networks, home of MTV, built its high volume polling, voting, and data collection service on MongoDB after moving from MySQL. Viacom was able to capture and analyze data at a large scale because of MongoDB’s flexible document data model and scale-out design.
- Experian Health chose MongoDB over MySQL to power its product ‘Universal Identification Manager’, a new application the company uses to uniquely identify healthcare customers. If the developers had to use the relational database model they would need to execute up to 10 SQL joins to positively match a patient’s identity which would be cumbersome. Using MongoDB the schema was simplified to remove complexity and drastically reduce the number of queries. It also improved the performance significantly.
Though many organizations have successfully migrated from an RDBMS to MongoDB, you cannot drop-in MongoDB as a replacement for legacy applications built around the relational data model and SQL. Organizations that have stable robust applications running on MySql will continue to do so. Organizations who have a community of developers who are well versed with SQL and do not handle huge volumes of data may still want to settle with MySQL.
So which is better MySQL or MongoDB?
Though MongoDB scores over MySQL because it is ‘magically’ faster and for its unmatched ability to handle large volumes of unstructured data, it is a new space. Hence there is a dearth of skills in the developer community. The nature of the applications and the data sets will determine the selection of the database and I will let the user determine his best choice based on the insights I have provided in this article.