MongoDB and MySQL Database are the two most popular databases amongst project architects who design the entire project from scratch. Both databases have different usage, and also both are different. Comparing MongoDB with MySQL is like a comparison between apples and oranges. This article tries to highlight the key differences between both of them. Let’s take a look at key differences between these two.
MongoDB - An Alternative to SQL DB?
It is a NoSQL document-oriented database and uses a JSON-like format to store data. MongoDB is widely popular for working with large distributed data. Many data advocates already project it as an alternative to traditional relational databases. The key feature of MongoDB is its support for dynamic schema, which means developers need not worry about the structure of your data, or in a broader sense, we can say that the requirement of tables and rows is eliminated with MongoDB. Obviously, with a dynamic schema, the queries would be quite different from a relational database. This helps the developers to play with multivariate data sets. You may consider checking out our following articles on how to setup MongoDB on aws or how to host mongodb on azure incase you are using Microsoft Azure cloud or Amazon AWS and want to deploy MongoDB in the cloud.
MySQL - A Traditional Approach
MySQL is a traditional relational database and is still quite popular amongst the development teams. It is still widely used for various application development projects. Unlike MongoDB, MySQL doesn’t offer the flexibility to store a variety of data. It supports only fixed schema, which means that the developers have to create a schema and store data in a format that adheres to ANSI standards. Oracle Corporation maintains MySQL. Incase, your project approves of using MySQL in AWS then you may consider checking this article on how to install MySQL on aws or how to setup Mysql on Azure if you are a using Microsoft Azure cloud.
Documents vs Rows
A document in a mongodb database will look like somewhat as shown below:
{_id: ObjectId("5gkfiy5642639b5af2c56689"),
email: "Johndoe@bbc.co.uk",
name: {given: "Jhon”, family: "Doe"},
age: 31,
addresses: [{label: "business",
street: “10 Downing Street”,
city: "London",
county: "London",
postal_code: "SW1A",
country: "UK"},
{label: "Home",
street: "111 Southern Ring Rd",
city: "Brighton",
county: "Sussex",
postal_code:"BN19RN"
country: "UK"}]
}
If you carefully study it, there is nothing fancy about the document, and it is JSON format data that is stored in MongoDB. MongoDB is a collection of documents, and each document is stored in JSON format. Each document can store different data types, which means each document will be separate from another document structure-wise.
Data is stored in rows in the tables in MySQL just like any other RDBMS. A row of data will look like as shown below:
| 1 | John | Doe | 31 | johndoe@bbc.co.uk |
Collections vs Tables
All the documents are stored in collections in MongoDB. A collection can be created by running createCollection command as shown below:
In mySQL all the data is stored in tables. A Table can be created by running the create table query as shown here:
The same data which has been stored in MongoDB as shown above is stored in two separate tables in mySQL.
Table User
| PersonId | FirstName | LastName | Age | |
| 1 | John | Doe | 31 | johndoe@bbc.co.uk |
Table Address
| AddressID | Label | StreetAddress | City | PostalCode | County | Country | PersonId |
| 1 | Business | 10 Downing Street | London | SW1A | London | UK | 1 |
| 2 | Home | 111 Southern Ring Rd | Brighton | BN19RN | Sussex | UK | 1 |
If you are a developer, you already know that data can be stored in traditional SQL databases in parent-child relationships, which is not the case in MongoDB. Child objects are stored as nodes within the same document. Remember, a child can contain another child, and so on. It is just like a tree structure where each branch can have several branches.
MQL vs SQL Queries
A MongoDB query is different than that of a traditional SQL query. For example, the query here fetches all the documents from the user collection.
MySQL uses standard SQL as shown below. The query fetches all the records from the user table.
Dynamic vs Flexible Schema
MongoDB supports dynamic schema, which means the developers need not worry much about defining the structure of the data to be stored. There is no need to define the variable type or field type for MongoDB. The database automatically creates the structure with the uploading of the data. What’s more, this structure can be altered as per the project’s requirement at any time.
On the other hand, MySQL requires the developer to define the schema before storing the data. The structure has to be specified before uploading the data. The developer has to predefine the variable types, character length, etc. A lot of thought and planning goes into designing the schema, and the schema cannot be altered once it has been created.
Architectural Difference
MongoDB is completely designed on distributed system architecture which gives the developers flexibility to work on distributed data in a cross-platform application.
MySQL is not designed on distributed system architecture but can support distributed database architecture.
Performance vs Data consistency
Since MongoDB has been designed on distributed system architecture, data can be uploaded from various streams quite fast and in real-time. But it doesn’t support the data consistency. This means that chances of data duplication are also high.
MySQL is not fast as compared to MongoDB as it accepts only structured data. This ensures that MySQL provides high data consistency.
Security
MongoDB has no SQL. Hence it is safe from SQL injection-related threats. However, it provides complete safety and is highly secured would be an overstatement. A DOS can be triggered by the use of a specific find query.
By visiting this link, you can find the list of issues reported. Even if it is safe from SQL injection, it is not safe from JavaScript-related injections that can be a serious issue.
The most common security threat that MySQL posses like any other SQL database is SQL injection. Other security issues include cross-site scripting, race condition, remote pre-auth user enumeration, and denial of service. You can visit this link to see the list of security vulnerabilities as reported.
Use Cases
The ideal use case for MongoDB includes blogging comments, content management, IoT and sensor data processing, sports data, and other applications where a lot of unstructured data is generated on a real-time basis.
The ideal use case for MySQL involves transaction-oriented data such as banking transactions, payment gateways. Other use cases include customer/client database, employee database, student database. If you are more concerned about data consistency rather than speed, then MySQL is the best choice.
Pros and Cons
So far, we have discussed some basic differences between MongoDB and MySQL. Let us now look at some pros and cons of each.
MongoDB
Pros
- Flexible Schema
- Faster Data read and write
- Safe against threat of sql injections
- Can be used with large volume of data
- Ideal for real time data analytics
- Easy to install and setup
- High speed performance and availability
- Provides Sharding of larger data sets
- Can be quickly scaled horizontally
Cons
- Not secure against Javascript injection
- Lack of data integrity and duplication of data
- Not suitable for transaction related work
- Uses high memory
- No Joins
- If indexing is implemented poorly the database would perform very slowly
- Document storage is limited to 16mb.
MySQL
Pros
- Widely popular
- Suitable for transaction related jobs
- Provides data integrity
- Offers Joins
- Provides query caching
Cons
- Does not fully comply with SQL standards
- Hard to scale
- Prone to SQL injection attacks
- Cross site scripting attack vulnerable
- Not suitable for large volume of data
Comparison Table
| Feature | MongoDB | MySQL |
| Launch Year | 2009 | 1995 |
| Maintained By | MongoDB Inc | Oracle Corporation |
| License | SSPL | GPLv2 |
| Type | NoSQL | RDBMS |
| Written in | C++, JavaScript, Python | C/C++ |
| Schema Type | Dynamic | Fixed |
| SQL supported | MQL | DML, DDL, DCL,TCL |
| Data type supported | Unstructured | Structured |
| Data Storing Format | JSON or BSON | Tables and Rows |
| Scaling | Horizontal | Vertical |
| Key Support | No | Yes |
| Joins Supported | No | Yes |
In this article, we studied the difference between MySQL and MongoDB. Both the database have their pros and cons. However, in the future, we will be generating more unstructured data as compared to structured data, so the role of MongoDB and other NoSQL databases would be more prominent. At the same time banking system is to stay forever and also, the various transaction-based systems are going to last as long as human race exists. For these situations and where the importance of structured data exists the role of MySQL becomes very important. Only time will tell which will be more effective and efficient.
