Once upon a time, it was thought that Relational Database Management Systems (RDBMS) were the way to go for any kind of data store. By the time MySQL came on the scene in 1995, E.F. Codd's famous paper on relational data model was 25 years old, and so SQL and relational databases were considered the default for data stores.
Indeed, with the convergence of these ideas and open source, and that fact that most data was collected (and searched) transactionally, and those transactions continued to be ACID (Atomic, Consistent, Isolated, and Durable) it was no surprise that MySQL and PostgreSQL ruled the day. And, for a while, everything worked just great! However, with the rise of big data in the mid- to late- 2000's, changes were afoot.
Eventually, webapps were deployed that captured very large volumes of highly-volatile, high-velocity data, and access to that data required high concurrency and multi-tenancy — up to millions of requests per second. Once this became a regular state of affairs, the overhead associated with purely SQL searches, including JOINs, could no longer meet users' speed benchmarks.
What's more, over time, as memory became cheaper and the need arose for purely textual searches, high availability, and variable-length data records in a single store, the industry begged for a more suitable solution. And thus MongoDB appeared on the scene.
But let's start from the beginning.
Overview of MySQL
Based on Structured Query Language (SQL), MySQL is an open source RDBMS which runs on most platforms, including Linux UNIX and Windows. As a leading choice for web-based applications, Facebook, Twitter, YouTube, and many more companies count themselves among its users. Although created by a Swedish company (MySQL AB in 1995) Oracle drives the product today "...delivering new capabilities to power next-generation web, cloud, mobile and embedded applications."
Although MySQL is most famously known as a player in the RDBMS space, it has evolved and changed considerably since its introduction. The new features these changes require are not always "relational". With the rollout of MySQL 8.0 in April 2018, MySQL now offers support for NoSQL document stores, in addition to bringing several improvements which implement transactional storage of dictionary data, JSON support, window functions, and common table expressions.
Overview of MongoDB
MongoDB, on the other hand, is a free and open source document-oriented database. Considered NoSQL, MongoDB uses JSON-like documents with schemas.
Relational databases were not designed to cope with challenges of scale and flexibility seen in applications today, and they do not take best advantage of memory, commodity storage, and processing power available now, either.
MongoDB addresses these challenges by the way its data is stored: in flexible JSON-like documents. This allows documents within the same collection to have variable numbers of fields, with the flexibility for data structures to change over time. MongoDB can use models that map easily to objects in your application code, ad-hoc queries, indexing, and real-time aggregation are easy, powerful, and readily accessible.
What's more, MongoDB is a distributed database with high availability, horizontal scaling, and geographic distribution.
MySQL and MongoDB feature comparison
|Popular relational DBMS||Popular Document Store|
|Implemented in||C and C++||C++|
|Holds||Relational tables, Document Store, Key-value store||Document Store, Key-value store|
|Organized by||Table, Row, Column||Collection, Document, Field|
|APIs and access methods||SQL, Proprietary native API, ADO.NET, JDBC, ODBC||JSON-like proprietary protocol|
|Server Operating Systems||Linux, OS X, Solaris, Windows, FreeBSD||Linux, OS X, Solaris, Windows|
|Best Used for||• Structured data
• Multi-row transactions
• Frequent updates and modifications
• Relatively small data sets
|• Unstable schemas
• Variable-length documents in a collection
• Exponential growth of data serially
• High Availability
|Popularity (according to DB-Engines rankings)||#2 Overall||#5 Overall|
Supported programming languages
MongoDB supports a slightly longer list of languages than MySQL, but some of the lesser-known languages supported are done so with unofficial drivers provided by the community. This list is constantly changing, so be sure to check online for the latest drivers on each.
MySQL works with various storage engines; additionally, it supports Unicode, triggers, and SSL. There is support for integrated replication also. Queries on MySQL can be cached, and the language supports SubSELECTs and Full-text search.
MongoDB also supports a variety of storage engines with a rich query language, comprehensive secondary indexes, and support for embedded data models. MongoDB is fast with in-memory speed. Finally, native replication and auto-sharding are both supported.
MySQL and MongoDB are two of the top five most popular forms of database management software, so rest assured, both of them will:
- Provide CRUD (Create, Read, Update, Delete) functionality and a query language;
- Support a litany of popular (and unpopular!) programming languages and operating systems;
- Enjoy support by large communities of experienced and knowledgeable engineers.
It's worth mentioning that with the releases of MySQL 8.0 and MongoDB 4.0, the feature sets are beginning to overlap. For example:
- MySQL has added support for NoSQL document stores and geospatial data;
- MongoDB now supports multi-document transactions.
However, we believe that it's generally better to go with features that are more stable and established within a given product. In other words, if you prefer NoSQL document stores (e.g. for geospatial data), it's probably better to consider MongoDB; on the other hand, if you need a relational database with ACID data integrity guarantees, then MySQL is likely a better choice.
Language and access
- MySQL uses SQL.
- MongoDB uses a structured query language based on JSON. To build a query, you must specify a document containing properties your desired results must match.
MySQL is better suited for smaller databases and a more general (as well as generally-known) solution, being based on SQL.
However, MongoDB takes advantage of in-memory speed, which makes it better-suited for handling very large, unstructured data sets.
Which is best for you?
It's important to keep in mind, not only what kind of data you have, but how much your data is going to grow. If you have a relatively small batch of mostly transactional data tied to a static schema, then MySQL may be your best bet. As a query language, SQL is a widely-known standard, with less of a learning curve for your team.
However, if your data is slated to grow exponentially over time and requires a variable schema, then MongoDB is likely the the way to go.
Whatever you do decide, always remember to assess your existing infrastructure and team to see what gaps you may need to address, not only in terms of existing infrastructure but also skills.
We hope that you find our comparison useful. At Alooma, we're here to help regardless of which approach you take. Don't hesitate to contact us to learn how a data integration solution can help you.