MongoDB vs. MySQL

Introduction

The wide variety of database management systems (DBMS) on offer is undoubtedly a good thing. However, it also means that choosing the right database system for your needs requires more than just going with the most popular option.

From transaction model differences to the quality of support, DBMSs come in all shapes and sizes.

In this comparison article, you will read about the differences between the two popular DBMS solutions: MongoDB and MySQL.

What is MongoDB?

MongoDB is a cross-platform NoSQL database management system, like Cassandra or Redis. However, unlike those systems, MongoDB is document-oriented. It utilizes JSON-like documents called BSON files as locations for its document stores. Since the BSON file is a modification of the JSON format, MongoDB is fully compatible with JavaScript Object Notation.

Another important feature of MongoDB is that it is schema-free. It does not require defining a fixed structure during document creation and makes it significantly easier to change the record structure later.

MongoDB’s development started in 2007, and in 2009 it became open-source. MongoDB Inc., the company behind the software, offers commercial support for the system.

At the time of writing this article, the latest release of MongoDB is 4.4.3 from December 2020.

What is MySQL?

MySQL is a relational database management system (RDBMS). It is an open-source implementation of SQL, Structured Query Language. Relational databases like MySQL, Oracle, or PostgreSQL, are similar because they organize data in tables. These tables contain related data types that help structure data.

MySQL stores related data in any number of separate tables. Querying and correlating data from those tables is facilitated by JOIN operations, which enable the creation of temporary tables and row sets using data from multiple tables.

Oracle is the company that owns and offers support for MySQL. At the time of writing this article, the latest stable release of MySQL is 5.7.32 from October 2020.

MongoDB Vs. MySQL: Comparison Overview

MongoDB and MySQL are two fundamentally different database management systems. Comparing them is useful because they are good representatives of their respective management philosophies. For further information, see NoSQL Vs. SQL.

The table below is a head-to-head comparison of the essential aspects of both DBMSs.

	MongoDB	MySQL
Database type	Document-oriented NoSQL	Relational DBMS
Programming language	C++, C, JavaScript	C++, C
Schema	Dynamic	Static
Data storage	BSON (JSON-like format)	Tables (rows and columns)
Query language	MQL (default), but works with many languages	SQL
MapReduce	Yes	No
Database conversion	Yes	No
Performance analysis	Yes	No
Transaction model	BASE	ACID
Isolation	No	Yes
Referential integrity	No	Yes
Complex transactions	No	Yes
Scaling	Horizontal	Vertical
Data locality	Yes	No
Auto sharding	Yes	No
Replication mode	Master-Slave	Master-Slave, Master-Master

Following is a detailed overview of the most significant areas of comparison for MongoDB and MySQL.

Database Structure (Schema)

MongoDB: MongoDB stores data in documents. These documents have various structures, depending on the database requirements. The system stores related data together to facilitate quick querying.

The flexibility of the document-oriented approach in MongoDB is one of its greatest advantages compared to MySQL and other relational DBMSs. MongoDB does not need a table schema to be declared before allowing data input.

A MongoDB document collection can feature documents with different sets of fields. The field data type can also vary across documents. Adding or removing fields inside a document is performed by simply updating the document structure. The same goes for changing field data types.

A write operation in MongoDB is atomic on the document level. This is true even in cases when an operation affects multiple embedded documents. Multi-document write operations maintain atomicity on the document level but are not atomic themselves.

MongoDB supports multi-document transactions on replica sets and sharded clusters.

MySQL: Data in MySQL is stored in tables, which are created before any data is added. Each database table consists of rows and columns. Rows represent database entities, and columns provide information about the entities.

Static schemas define the database structure and require that all rows have the same structure. MySQL has a hard limit of 4096 columns per table and a maximum row size of 65,535 bytes.

The system schema consists of tables containing the information necessary for the MySQL server’s uninterrupted function. This information includes data dictionary tables with database object metadata and other operational system tables.

Due to the rigidity of MySQL’s architecture, database creation and management require a lot of planning ahead to maintain high performance when the database starts growing.

Index Optimization

MongoDB: MongoDB uses indexes for optimization. Without indexes, MongoDB goes through every document in a collection to find a match for a query.

MySQL: Just like MongoDB, MySQL employs indexes to optimize querying. If there are no relevant indexes for a particular query, MySQL searches the entire table.

Database Deployment

MongoDB: Users can natively deploy MySQL on Microsoft Windows, OS X, Linux, and Solaris. MongoDB is also well suited for distributed environments like Docker containers.

MySQL: MySQL has binaries for various operating systems, such as Microsoft Windows, OS X, Linux, FreeBSD, NetBSD, Solaris, etc. The officially maintained MySQL Docker images are built only for Linux platforms.

Query Language

MongoDB: MongoDB’s engine can interact with many different programming languages, depending on the programmer’s preference. Out of the box, however, MongoDB uses MQL (MongoDB Query Language).

MQL is an SQL-like language designed for querying unstructured data. Queries in MongoDB are JavaScript based.

For example, this is how a collection is created in MongoDB:

db.fruit.insertMany([
   { item: "apple", qty: 2, color: “red”, status: "A" },
   { item: "pear", qty: 5, color: “yellow”, status: "A" },
   { item: "cherry", qty: 4, color: “red”, status: "A" }
]);

Querying a collection is performed using the find command:

db.fruit.find( { color: "red" } )

MySQL: Like many RDBMS, MySQL uses SQL. The example of the syntax uses the data from the MQL example above.

First, MySQL users must create a table:

CREATE TABLE IF NOT EXISTS `fruit` (
  `item` varchar(200) NOT NULL,
  `qty` int(3) unsigned NOT NULL,
  `color` varchar(200) NOT NULL,
  `status` varchar(200) NOT NULL,
  PRIMARY KEY (`qty`)
) DEFAULT CHARSET=utf8;

Next, the table is populated with data:

INSERT INTO `fruit` (`item`, `qty`, `color`, `status`) VALUES
  ('apple', '2', 'red', 'A'),
  ('pear', '5', 'yellow', 'A'),
  ('cherry', '4', 'red', 'A');

Creating the view to filter data:

CREATE VIEW red_fruit AS SELECT item FROM fruit WHERE color = 'red';

Once the schema exists, querying the table is performed using the SELECT statement:

SELECT * FROM red_fruit;

Data Replication and Clustering

MongoDB: The purpose of data replication is to offer business continuity and data availability. MongoDB keeps multiple replicas of data on isolated servers. This replication helps prevent data loss due to unforeseen circumstances like server crashes, power outages, etc.

A replica set is a set of mongod instances managing the same data set. Being the primary MongoDB daemon process, mongod manages data requests, controls data access, and conducts background operations. A replica set consists of a number of data nodes, where one is the primary node that handles all write operations, while the others are secondary.

Whenever a replica set is initiated, or there is a change in the set relating to the number of nodes, such as the addition of a new node or losing a connection with the primary node for more time than allowed, the system triggers auto-elections. The purpose of elections is to determine whether the current primary data node should remain primary.

MongoDB also supports sharding, which enables horizontal scaling through shard clustering. Sharding is a method of data distribution across multiple computers, which enables reading and writing data along a number of nodes. No single node manages the whole data, so the data is distributed across all the nodes in a shard.

When working with large data sets and high throughput, sharding helps decrease the load on a single server and enables scaling through the addition or removal of servers, depending on the need.

MySQL: MySQL supports the following types of replication out of the box:

• Asynchronous master-slave replication, where the process goes from one master to any number of slaves, and vice versa. The master does not wait for the slaves to confirm the reception of the event. The changes to the database are queued up and written at a later time.
• Semi synchronous master-slave replication, with master waiting on replication for just one slave to confirm the reception of the event.
• Synchronous multi-master replication, allowing data to be stored on multiple computers and updated by any group member. All slaves are required to confirm the reception of events.

While MySQL does not support auto sharding and clustering on its own, users can achieve some of the functionalities through the MySQL Cluster technology.

Security

MongoDB: MongoDB uses a role-based authentication. Other security features include auditing and authorization, and TLS and SSL support for encryption purposes.

MongoDB’s main security-related advantage over MySQL is its resistance to SQL injection attacks. SQL injection is a vulnerability enabling an attacker to gain access to the database queries made by an app. The attacker may use this vulnerability to gain access to, modify, or delete app data. An SQL injection can even compromise the server itself.

Given that MongoDB’s architecture uses JSON-like documents to explain the query and that queries are represented as secure Binary JSON (BSON) objects, direct string injection is impossible.

MySQL: MySQL uses a privilege-based security model. Each user is authenticated and given specific privileges to create, query, or edit tables. SSL provides connection encryption.

SQL injection attacks are MySQL’s biggest vulnerability. However, there are certain measures that can be taken in order to lower the risk of an attack, such as using parameterized queries instead of a concatenated user input. This is particularly important in WHERE clauses and INSERT or UPDATE statements.

Scalability

MongoDB: Horizontal scaling is one of the main advantages of MongoDB in comparison to MySQL. Using shard clustering, MongoDB distributes data across machines, which helps use cases that include working with large data sets.

MySQL: As mentioned above, MySQL does not support sharding out of the box. Scaling a database in MySQL (and many other popular RDBMS solutions) requires either changing the application using the database or allowing for downtime until the migration to a larger server is complete.

Speed and Performance

MongoDB: As a NoSQL database, MongoDB is faster than MySQL due to its querying model, which allows for variations depending on the type and size of work. MongoDB’s speed is especially obvious in scenarios that include large databases.

MySQL: While MySQL is the slower of the two, its performance can be significantly improved with tuning.

Support and Documentation

MongoDB: MongoDB Inc. offers 24/7 Enterprise-Grade Support and an add-on for extended lifecycle support. The support includes a rich knowledge base, maintenance releases, updates, patches, and bug fixes. Being a newer player on the market, MongoDB has a smaller online community than MySQL.

MySQL: As an Oracle product, MySQL offers 24/7 Oracle Lifetime Support in three tiers: Premier support includes versions up to 5 years old, Extended tier is for the versions between 6 and 8 years old, while Sustain supports all versions older than eight years. A very active online community helps users quickly find solutions to many problems.

Use Cases

MongoDB: MongoDB, like other NoSQL solutions, performs best in the use cases which require a flexible and fluid way to manipulate data, such as:

• Real-time analytics
• Data warehouses
• Mobile apps
• Content management systems

The list of companies using MongoDB in their tech stacks includes Lyft, Adobe, Codecademy, etc.

MySQL: Institutions that deal with financial transactions and others requiring ACID compliance choose MySQL over MongoDB. The list of companies using MySQL is longer and includes Airbnb, Netflix, Amazon, Shopify, etc.

It is important to note that some companies, like Uber, use both MongoDB and MySQL for different use scenarios within the company.

How to Choose?

There is no clear winner in the comparison between MongoDB and MySQL because these two database management systems cater to two significantly different fields. Choosing the right solution requires a careful analysis of one’s particular needs.

Choose MongoDB if you need to:

• Increase availability – MongoDB’s data replication technology and the use of multiple nodes for data storage ensures higher availability. MySQL can replicate data across nodes as well, but the process is complex and time-consuming.
• Speed up development – the flexibility of the JSON format is particularly beneficial for app developers, who prefer representing data in arrays and subdocuments rather than in rigid rows and columns.
• Scale horizontally – MongoDB allows for horizontal scaling without making changes in the application or going through a downtime.

Choose MySQL if you:

• Deal with secure money transactions – MySQL transactions are treated as single units, and they do not clear until each individual operational stage is successfully completed. If any of the stages fail, the entire operation fails. This feature of MySQL ensures data integrity necessary for financial transactions.
• Need comprehensive transactional support – MySQL transactions follow the ACID standard, which means that they are atomic, consistent, isolated, and durable.
• Need better support – MySQL has been around for much longer than MongoDB, so finding solutions for common problems on the internet is much easier if you use MySQL. On a higher level, managing a NoSQL database such as MongoDB requires more technical expertise than is the case with MySQL.

Conclusion

This comparison article highlighted all the essential differences and similarities between MongoDB and MySQL. After reading it, you should be able to make an informed decision regarding which database management system to choose.