MySQL vs. MongoDB: Differences Explained

Introduction

Selecting the right database management system (DBMS) is essential for any successful software development project. The transaction model, performance, scalability, and support quality dictate the most optimal use cases for each DBMS.

This comparison article explains the differences between the popular DBMS solutions - MongoDB and MySQL.

MySQL vs. MongoDB: An Overview

As a relational DBMS, MySQL utilizes a structured, tabular data handling model. In contrast, MongoDB, a NoSQL database, employs a flexible, document-oriented approach. These contrasting models (SQL vs. NoSQL) underlie many key distinctions between these systems.

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

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

MySQL vs. MongoDB: In-Depth Comparison

Although both tools are DBMSs, MySQL and MongoDB significantly differ in approach to data storage, query processing, scalability, etc. This comparison will outline the strengths and limitations of each database to help identify the right choice for different application requirements.

Definitions

MySQL is an open-source relational DBMS that implements the SQL standard. Relational databases like MySQL, Oracle, or PostgreSQL structure data in tables with well-defined columns and data types.

MySQL can manage related data distributed across multiple tables. The JOIN operation allows for efficient querying and correlation of data from these tables, enabling the creation of temporary datasets by combining rows from different sources.

MongoDB is a cross-platform, document-oriented NoSQL DBMS. It differs from other NoSQL systems like Cassandra or Redis in that it employs a flexible schema-less approach to store data in BSON documents. The BSON format is an extension of JSON, compatible with JSON data.

MongoDB's schema-less nature eliminates the need to define a rigid structure upfront. This feature allows dynamic modifications to document fields and their associated data types.

Database Structure (Schema)

MySQL stores data in tables, each database table consisting 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 to maintain high performance when the database starts growing.

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 MongoDB documents is one of its most significant advantages over MySQL and other relational DBMSs. MongoDB does not need a declared table schema 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. The user adds or removes fields inside a document by updating the document structure. The same goes for changing field data types.

A write operation in MongoDB is atomic on the document level, even 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.

Index Optimization

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

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

Database Deployment

MySQL has binaries for various operating systems, such as Microsoft Windows, OS X, Linux, FreeBSD, NetBSD, Solaris, etc. The official MySQL Docker images exist only for Linux.

MongoDB can natively run on Microsoft Windows, OS X, Linux, and Solaris. It is also well-suited for distributed environments like Docker containers.

Query Language

While MySQL uses SQL (Structured Query Language), an industry-standard language for interacting with relational databases, MongoDB employs a more flexible, JSON-like query syntax suited for its NoSQL nature. The sections below illustrate the differences between the query languages used by MySQL and MongoDB.

MySQL Queries

The example below illustrates using SQL to create a table, add data, and perform a query:

1. Create a database:

CREATE DATABASE `test`;

2. Select the database:

USE `test`;

3. Create a table with the CREATE TABLE statement:

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;

4. Populate the table with data:

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

5. Create a view to filter data:

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

6. Query the table with the SELECT statement:

SELECT * FROM red_fruit;

MongoDB Queries

MongoDB 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 for querying unstructured data. Queries in MongoDB are JavaScript-based.

For example, this is how to create a collection in MongoDB:

1. Create a collection named fruit and populate it with data:

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" }
]);

2. Query a collection with the find command and filter results according to fields and values:

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

Data Replication and Clustering

The purpose of data replication is to provide business continuity and data availability. The following sections compare the data replication capabilities of MySQL and MongoDB.

Replication in MySQL

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

• Asynchronous master-slave replication. The process goes from one master to many slaves and vice versa. The master does not wait for the slaves to confirm the event's reception. Instead, it queues up changes to the database and writes them at a later time.
• Semi-synchronous master-slave replication. The master waits on replication for just one slave to confirm the event's reception.
• Synchronous multi-master replication. Data can be stored on multiple computers and updated by any group member. All slaves must confirm the reception of events.

While MySQL does not natively support auto-sharding and clustering, users can achieve some functionalities through the MySQL Cluster technology.

Replication in MongoDB

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 several 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 (e.g., adding a new node or losing a connection with the primary node for more time than allowed), the system triggers auto-elections. Elections aim 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 several nodes. No single node manages all the 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 by adding or removing servers.

Security

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

SQL injection attacks are MySQL's most significant vulnerability. SQL injection is a vulnerability enabling an attacker to access the database queries made by an app. The attacker may use this vulnerability to access, modify, or delete app data.

Specific measures can be taken to lower the risk of an SQL injection attack, such as using parameterized queries instead of concatenated user input (e.g., in WHERE clauses and INSERT or UPDATE statements).

MongoDB uses role-based authentication, auditing, authorization, and TLS/SSL support for encryption. MongoDB's main security-related advantage over MySQL is its resistance to SQL injection attacks.

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.

Scalability

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. MySQL does not support sharding out of the box.

MongoDB's horizontal scaling feature represents another significant advantage over MySQL. Using shard clustering, MongoDB distributes data across machines, which helps use cases that include working with large data sets.

Speed and Performance

Out of the box, MySQL is slower than MongoDB. However, its performance can be significantly improved with tuning.

As a NoSQL database, MongoDB's speed comes from its querying model, which allows for variations depending on the type and size of work. MongoDB's speed is evident in scenarios that include large databases.

Support and Documentation

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

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 in the market, MongoDB has a smaller online community than MySQL.

Use Cases

Institutions that deal with financial transactions and others requiring ACID compliance choose MySQL over 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

MySQL vs. MongoDB: How to Choose

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

Choose MySQL for:

• Secure money transactions. MySQL transactions are treated as single units. They do not clear until each operational stage is completed. If any of the stages fail, the entire operation fails. This feature of MySQL ensures data integrity, which is necessary for financial transactions.
• Comprehensive transactional support. MySQL transactions follow the ACID standard, i.e., they are atomic, consistent, isolated, and durable.
• Better support. MySQL has existed longer than MongoDB, so finding solutions for everyday problems online is much easier with MySQL. On a higher level, managing a NoSQL database such as MongoDB requires more technical expertise than MySQL.

Choose MongoDB to:

• Increase app availability. MongoDB's data replication technology and multiple nodes for data storage ensure higher availability. MySQL can replicate data across nodes, 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 downtime.

Conclusion

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

If your project demands container orchestration, read how to Run MongoDB on Kubernetes.