MySQL is a popular, free-to-use, and open-source relational database management system (RDBMS) developed by Oracle. As with other relational systems, MySQL stores data using tables and rows, enforces referential integrity and uses structured query language (SQL) for data access. When users need to retrieve data from a MySQL database, they must construct an SQL query that joins multiple tables together to create the view on the data they require.
Database schemas and data models need to be defined ahead of time, and data must match this schema to be stored in the database. This rigid approach to storing data offers some degree of safety, but trades this for flexibility. If a new type or format of data needs to be stored in the database, schema migration must occur, which can become complex and expensive as the size of the database grows.
MongoDB is also free to use and open source; however, its design principles differ from traditional relational systems. Often styled as a non-relational (or NoSQL) system, MongoDB adopts a significantly different approach to storing data, representing information as a series of JSON-like documents (actually stored as binary JSON, or BSON), as opposed to the table and row format of relational systems.
MongoDB documents consist of a series of key/value pairs of varying types, including arrays and nested documents; however, the key difference is that the structure of the key/value pairs in a given collection can vary from document to document. This more flexible approach is possible because documents are self-describing.
The core differences between these two database systems are significant. Choosing which one to use is really a question of approach rather than purely a technical decision.
MySQL is a mature relational database system, offering a familiar database environment for experienced IT professionals.
MongoDB is a well-established, non-relational database system offering improved flexibility and horizontal scalability, but at the cost of some safety features of relational databases, such as referential integrity.
Which one should you choose?
In the following sections, we’re going to look at some of the different considerations when deciding between MongoDB and MySQL.
MongoDB is an attractive option to developers. Its data storage philosophy is simple and immediately understandable to anybody with programming experience.
MongoDB stores data in collections with no enforced schema. This flexible approach to storing data makes it particularly suitable for developers who may not be database experts, yet want to use a database to support the development of their applications.
Compared to MySQL, this flexibility is a significant advantage: To get the best out of a relational database, you must first understand the principles of normalization, referential integrity, and relational database design.
With the ability to store documents of varying schemas, including unstructured data sets, MongoDB provides a flexible developer interface for teams that are building applications that don’t need all of the safety features offered by relational systems. A common example of such an application is a web application that doesn't depend on structured schemas; it can easily serve unstructured, semi-structured, or structured data, all from the same MongoDB collection.
MySQL is a common choice for users who have extensive experience using traditional SQL scripting, designing solutions for relational databases, or who are modifying or updating existing applications that already work with a relational system. Relational databases may also be a better choice for applications that require very complex but rigid data structures and database schemas across a large number of tables.
A common example of such a system could be a banking application that requires very strong referential integrity and transactional guarantees to be enforced to maintain exact point-in-time integrity of data.
However, it is important to clarify that MongoDB also supports ACID properties of transactions (atomicity, consistency, isolation, and durability). This enables greater flexibility in building a transactional data model that can horizontally scale in a distributed environment and has no impact on performance for multi-document transactions.
A key benefit of the MongoDB design is that the database is extremely easy to scale. Configuring a sharded cluster allows a portion of the database, called a shard, to also be configured as a replica set. In a sharded cluster, data is distributed across many servers. This highly flexible approach allows MongoDB to horizontally scale both read and write performance to cater to applications of any scale.
A replica set is the replication of a group of MongoDB servers that hold the same data, ensuring high availability and disaster recovery.
With a MySQL database system, options for scalability are much more limited. Typically, you have two choices: vertical scalability, or adding read replicas. Scaling vertically involves adding more resources to the existing database server, but this has an inherent upper limit.
Read replication involves adding read-only copies of the database to other servers. However, this is typically limited to five replicas in total, which can only be used for read operations. This can cause issues with applications that are either write-heavy, or write and read regularly for the database, since it’s common for replicas to lag behind the write master. Multi-master replication support has been added to MySQL, but its implementation is more limited than the functionality available in MongoDB.
Assessing the performance of two completely different database systems is very difficult, since both management systems approach the task for data storage and retrieval in completely different ways. While it’s possible to directly compare two SQL databases with a set of standard SQL benchmarks, achieving the same across non-relational and relational databases is much more difficult and subjective.
For example: MySQL is optimized for high performance joins across multiple tables that have been appropriately indexed. In MongoDB, joins are supported with the $lookup operation, but they are less needed due to the way MongoDB documents tend to be used; they follow a hierarchical data model and keep most of the data in one document, therefore eliminating the need for joins across multiple documents.
MongoDB is also optimized for write performance, and features a specific insertMany() API for rapidly inserting data, prioritizing speed over transaction safety wherein MySQL data needs to be inserted row by row.
Observing some of the high-level query behaviors of the two systems, we can see that MySQL is faster at selecting a large number of records, while MongoDB is significantly faster at inserting or updating a large number of records.
This is an easy one, and a hands down win for MongoDB. The schemaless design of MongoDB documents makes it extremely easy to build and enhance applications over time, without needing to run complex and expensive schema migration processes as you would with a relational database.
With MongoDB, there are more dynamic options for updating the schema of a collection, such as creating new fields based on an aggregation pipeline or updating nested array fields. This benefit is particularly important as databases grow in size. In contrast, larger MySQL databases are slower to migrate schemas and stored procedures that can be dependent on the updated schemas. MongoDB’s flexible design makes this much less of a concern.
It’s worth pointing out that both databases have a lot in common. Both are free to get started with, both are easy to install on Linux and Windows, and both have wide programming language support for popular languages like Java, node.js, and Python.
In addition, MongoDB offers MongoDB Atlas, a managed cloud solution which is also forever free to use for exploratory purposes, while for a MySQL managed cloud version, you would need to have an account with one of the major public cloud providers and fall within their free tier terms in order to not pay.
MongoDB leverages the popular role-based access control model with a flexible set of permissions. Users are assigned to a role, and that role grants them specific permissions over data sets and database operations. All communication is encrypted with TLS, and it’s possible to write encrypted documents to MongoDB data collections using a master key which is never available to MongoDB, achieving encryption of data at rest.
MySQL supports the same encryption features as MongoDB; its authentication model is also similar. Users can be granted roles but also privileges, giving them permissions over particular database operations and against particular data sets.
In this article, we have talked about the main differences between MongoDB and MySQL, a schemaless non-relational database system and a relational database system, respectively. We have explained when it is better to use one over the other. We have discussed the scalability, performance, and user-friendliness for each system. Finally, we have also explained the flexibility and security features for both database systems from a comparison point of view.
Another option to consider is a hybrid deployment approach - giving you the benefit of both worlds, and the flexibility to choose the tool that works for you. Check out this hybrid deployment guide for more details.
Organizations of all sizes are adopting MongoDB, especially as a cloud database, because it enables them to build applications faster, handle highly diverse data types, and manage applications more efficiently at scale.
Development is simplified as MongoDB documents map naturally to modern, object-oriented programming languages. Using MongoDB removes the complex object-relational mapping (ORM) layer that translates objects in code to relational tables. MongoDB’s flexible data model also means that your database schema can evolve with business requirements. MySQL's rigid relational structure adds overhead to applications and slows developers down as they must adapt objects in code to a relational structure.
MongoDB can also be scaled within and across multiple distributed data centers, providing new levels of availability and scalability previously unachievable with relational databases like MySQL. As your deployments grow in terms of data volume and throughput, MongoDB scales easily with no downtime, and without changing your application. In contrast, achieving scale with MySQL often requires significant custom engineering work.
Both databases support a rich query language.
MySQL, like many relational databases, uses structured query language (SQL) for access.
MongoDB uses the MongoDB Query Language (MQL), designed for easy use by developers. The documentation compares MQL and SQL syntax for common database operations.
Database performance can vary widely depending on a number of factors - database design, application query patterns and load on the database being just a few. Since MongoDB's document model stores related data together, it is often faster to retrieve a single document from MongoDB than to JOIN data across multiple tables in MySQL.
Many customers have evaluated and selected MongoDB over MySQL, both because of better performance at scale and for radical improvements to developer productivity.
For the reasons discussed above, MySQL and other relational databases have added support for JSON. However, simply adding a JSON data type does not bring the developer productivity benefits of a document database to MySQL. Why? Because MySQL’s approach can detract from developer productivity, rather than improve it. Consider the following:
Proprietary Extensions to SQL: Querying and manipulating the contents of a JSON document requires the use of separate MySQL-specific SQL functions to access values, which will not be familiar to most developers. In addition, they are not supported or recognized by 3rd party SQL tools, such as BI platforms, data warehouse connectors, ETL and ESB pipelines, and more.
Legacy Relational Overhead: Even with JSON support, MySQL users are still tied to multiple layers of SQL/relational functionality to interact with JSON data – low level JDBC/ODBC drivers and Object Relational Mappers (ORMs). These layers impose high learning overhead. ORMs are also generally recognized as hard to optimize for performance and query efficiency – even for experienced relational developers. In addition, query optimization statistics for JSON data are more limited than those maintained for regular relational data types.
Complex Data Handling: When using JSON data, MySQL drivers do not have the capability to properly and precisely convert JSON into a useful native data type used by the application. This includes different types of numeric values (e.g. floating points, 64-bit integers, decimals) timestamps, and dates, or a Map or List in Java or a Dictionary or List in Python. Developers have to manually convert text-based JSON in their application, losing the ability to have fields that can take on multiple data types in different documents (polymorphism) and making the computation, sorting and comparison of values difficult and error-prone.
No Data Governance: MySQL offers no native mechanism to validate the schema of JSON inserted or updated in the database, so developers need to add either application or database-side functionality to apply governance controls against the data.
Schema Rigidity: MySQL users still need to define a schema for their regular relational data. If the schema is then modified to accommodate new application requirements, the table is locked for some operations until existing data is copied into the new schema, requiring applications to be quiesced during schema migration.
In this white paper, you'll learn:
Step by step how to migrate from a relational database to MongoDB.
The relevant technical considerations, including differences between relational and document data models and the implications for schema design.
Indexing, queries, application integration and data migration.