Data Management

A Comparative between MongoDB and MySQL

Written by Matan Sarig|April 10, 2017

MySQL has become an inexpensive option for organizations around the globe that need a relational database. However, as the variety and volume of data has increased in recent years, non-relational databases like MongoDB have arisen to meet the new needs of our fluid data.

 

Who Uses These Databases?

MySQL: MySQL has been maturing since 1995 and has grown a large following. Some organizations that use MySQL include Pinterest, Twitter, YouTube, Netflix, Spotify, US Navy, NASA, Walmart, and Paypal.

MongoDB: MongoDB was released in 2009 and is used by many organizations including Klout, Citrix, Twitter, T-Mobile, Zendesk, Sony, Hootsuite, SurveyMonkey, MuleSoft, Foursquare, and InVision.

 

What About Database Structure?

MySQL: MySQL stores its data in tables and uses the structured query language (SQL) to access the data. MySQL uses schemas to define the database structure, requiring that all rows within a table have the same structure with values being represented by a specific data type.

MongoDB: In MongoDB, data is stored in JSON-like documents that can have varied structures. To improve query speed, MongoDB can store related data together, which is accessed using the MongoDB query language. MongoDB is schema-free, allowing you to create documents without having to define the structure of the document first. These documents can be easily changed by adding or deleting fields.

In MongoDB, documents are able to have their own unique structure. New fields can be added at any time and contain any type of value. This type of functionality would require a relational database to be restructured.

While keys have to be unique for a document, you're allowed to have the same key used within other documents.

Using the MongoDB data model, you can represent hierarchical relationships, data arrays, and other complex structures in the database. In some cases, MongoDB performance is improved over MySQL because MongoDB does not use joins to connect data, improving performance.

 

Are Indexes Needed?

Both MySQL and MongoDB use indexes to allow them to find data quickly.

MySQL: With MySQL, if an index is not defined, the database engine must scan the entire table to find all relevant rows.

MongoDB: In MongoDB, if an index is not found, every document within a collection must be scanned to select the documents that provide a match to the query statement.

 

How Are Their Queries Different?

Selecting records from the customer table:

MySQL: SELECT * FROM customer

MongoDB: db.customer.find()

 

Inserting records into the customer table:

MySQL: INSERT INTO customer (cust_id, branch, status) VALUES ('appl01', 'main', 'A')

MongoDB: db.customer.insert({ cust_id: 'appl01', branch: 'main', status: 'A' })

 

Updating records in the customer table:

MySQL:  UPDATE customer SET branch = 'main' WHERE custage > 2

MongoDB:  db.customer.update( { custage: { $gt: 2 } }, { $set: { branch: 'main' } }, { multi: true } )

MySQL can be subject to SQL injection attacks, making it vulnerable. Since MongoDB uses object querying, where documents are passed to explain what is being queried, it reduces the risk of attack as MongoDB doesn’t have a language to parse.

 

Where (And How) Are These Databases Deployed?

MySQL: MySQL is written in C and C++ and has binaries for the following systems: Microsoft Windows, OS X, Linux, AIX, BSDi, FreeBSD, HP-UX, IRIX, NetBSD, and more.

MongoDB: MongoDB was written in C++, C, and JavaScript and has binaries for the following systems: Linux, OS X, Solaris, and Windows.

Panoply has connectors for MySQL and MongoDB providing a no-coding solution that does not require data preparation or transformation. This means you can consolidate data from MySQL, MongoDB, cloud, and more into a single data management platform.

 

What Types Of Replication / Clustering Are Available?

MySQL: MySQL supports master – slave replication and master – master replication (as of MySQL 5.7.6 and later). Multisource replication allows you to replicate from several masters in parallel.

MongoDB: MongoDB supports built-in replication, sharding, and auto-elections. Using auto-elections, you can set up a secondary database to automatically take over if the primary database fails. Sharding allows for horizontal scaling, which is difficult to implement in MySQL.

MongoDB uses replica sets to create multiple copies of the data. Each member of the replica set can have the role of primary or secondary at any point in the process. Reads and writes are done on the primary replica by default and then replicated to the secondary replicas.

 

Who's Currently Behind The Databases?

MySQL: MySQL was founded by the Finnish/Swedish company MySQL AB, which was started by David Axmark, Allan Larsson, and Michael "Monty" Widenius. In 1995, the first version of MySQL was released. The original version was designed for personal use, but later evolved to be an enterprise-grade database. In 2008, Sun Microsystems bought MySQL AB. In 2009, Oracle acquired Sun Microsystems and got MySQL as part of the deal.

MySQL is currently owned by the Oracle Corporation.

MongoDB: 10gen started developing MongoDB in 2007 with the idea for the name coming from the word "humongous". It was released in 2009 and has become a popular NoSQL database. 10gen was later renamed MongoDB, Inc. and continues to do development on the software as well as sales for their enterprise solution.

 

Who Provides Support?

MySQL: MySQL offers Oracle Lifetime Support at three levels: Premier for versions 1 - 5 years old, Extended for versions 6 - 8 years old, and Sustain for those that want to use the same version for 9+ years. Each level offers 24 x 7 support with knowledge base, maintenance releases, bug fixes, patches, and updates.

MongoDB: MongoDB offers Enterprise Grade Support that "extends beyond break/fix". This gives you 24 x 7 support as well as an extended lifecycle support add-on, which allows you the flexibility to upgrade to newer versions at your own pace. Support for MongoDB gives you unlimited access to support, security fixes, updates, and more.

 

Who Supplies Ongoing Development?

MySQL: Ongoing development is done by the Oracle Corporation.

MongoDB: Ongoing development is done by MongoDB, Inc.

 

Who Maintains The Documentation?

MySQL: The MySQL documentation is maintained by the Oracle Corporation and can be found at dev.mysql.com/­doc

MongoDB: The MongoDB documentation is maintained by MongoDB, Inc. and can be found at https://docs.MongoDB.com/

 

Is There An Active Community? 

MySQL: The MySQL database is owned and maintained by the Oracle Corporation. As such, they offer a Developers Zone on the MySQL website. This resource can be found at https://forums.mysql.com/.

Additional information can also be found at:

MySQL Wiki

Oracle MySQL Events

MySQL Events

List of MySQL user groups

MongoDB: "One advantage of MySQL over NoSQL like MongoDB is that the community in MySQL is much better than NoSQL. This is mostly because NoSQL is relatively new while MySQL has been around for several years," says Jenny Richards at analyticbridge.com.

The MongoDB community can be found at https://www.MongoDB.com/community. This page provides a variety of events, webinars, user groups, and MongoDB University.

 

Which Database Is Right For Your Business?

When making the choice between MySQL and MongoDB, there are a variety of factors to consider:

MySQL: There are many use cases for a relational database like MySQL. Any type of application that requires multi-row transactions such as an accounting system, would be better suited for a relational database. MongoDB is not an easy replacement for legacy systems that were built for relational databases.

MongoDB: On the other hand, there are a variety of use cases where MongoDB is well-suited. Some of these include real-time analytics, content management, the internet of things, mobile, and other types of applications that are new and can take advantage of what MongoDB has to offer.

Some scenarios where MongoDB may be an option include:

No clear schema definition- There are several instances where MongoDB can be a good choice when dealing with databases with no clear schema definition.

In situations where:

  • During the design phase, you can't define the schema for your database.
  • You find you are de-normalizing a database schema. In MongoDB, documents can be used to store unstructured data and make it easier to update and retrieve.
  • Your database is growing, but your schema is not stable. In MySQL, a table can degrade if it goes over 10 GB, MongoDB does not have this issue.

When adding new columns to a relational database like MySQL, it can lock up the entire database and cause performance issues. With MongoDB, since it is schema-less, you can add new fields and it won't affect existing rows.

Write load is high- For an environment that has many writes compared to reads, MongoDB can be a good choice. MongoDB was written to allow a high insert rate and doesn't have to worry about transaction safety, unlike MySQL.

No database administrator- If you don't have a database administrator, MongoDB may be worth considering.

You lose connectivity to your other databases- If you have an environment without reliable connectivity to your other servers, the high availability of MongoDB may be helpful.

 MySQL vs MongoDB.png 

MySQL and MongoDB both have their strengths and weaknesses. If your data requires multi-row transactions or you have to support a legacy application, a relational database may be the right choice for your organization. However, if you need a more flexible, schema-free solution that can work with unstructured data, MongoDB is worth considering.

Regardless of which one you select, Panoply can create a single data management that connects your MySQL, MongoDB, cloud and more with no coding required.

 

From raw data to analysis in under 10 minutes.

Sign up now for a demo or a free trail of the Panoply.io platform.

Learn more about platform features