Understand the main differences between MySQL and MongoDB through this in-depth review and comparison of MongoDB vs MySQL databases:
In today’s world, both relational and nonrelational databases are being used depending upon the type of applications.
Relational databases provide high levels of consistency and transaction guarantees while Document-based (or Nonrelational databases) provide high throughput, redundancy, and scale.
In this tutorial, we will compare the differences between relational database MySQL and a non-relational document-based database – MongoDB.
=> Click here for the complete MySQL tutorial series
Table of Contents:
Understanding MySQL And MongoDB
Introduction to MongoDB
MongoDB is a popularly used document-oriented NoSQL database. It was developed and currently maintained by MongoDB Inc.
So in a nutshell, MongoDB stores data in the form of documents which provides schema independence to a large extent but provides limited query capabilities as opposed to MySQL. Every document stored in MongoDB needs to have a unique key.
Introduction to MySQL
MySQL is an open-source RDBMS. It was built by MySQL but is now owned by Oracle.
The data is stored in tables consisting of rows and columns. Almost all relational databases provide ACID or transaction level compliance. The query interface provided by MySQL is rich with JOIN operations to correlate different tables with each other.
One of the main issues with MySQL is scalability. We can just increase the hardware power/storage and cannot take advantage of distributed systems or horizontal scaling.
MongoDB Vs MySQL
Let’s see a summary of the comparison between MySQL and MongoDB, and then we can look at important sections in detail.
| Area | MySQL | MongoDB |
|---|---|---|
| Schema | RIgid - Columns need to be defined before used | Flexible |
| OS Support | Multiple | Multiple |
| Cloud/Web | ? | ? |
| Data Storage | Rows & Columns | JSON docs |
| Query Language | SQL | Constructed using query objects |
| JOINS | JOINS are available to query and correlate tables | Not supported - 2 Mongo DB tables cannot be queries together |
| Model | ACID | |
| Consistency | Always consistent | Eventually Consistent |
| Transactions | ? | ? |
| Referential Integrity | Supported using concept of FOREIGN KEYS | Not supported |
| Secondary Index | ? | ? |
| Composite Unique Keys | ? | ? |
| Full text search | ? | ? |
| Data Hierarchy | Database -> tables -> rows/cols | Database -> collections->documents |
| Application types | Owing to the ACID compliance, MySQL is suited for applications requiring transactional capabilities - Some examples include - - Banking applications - Trading applications | Mongo owing to its loose coupling with schema definition is suited for applications having diverse data and no defined data structures Social Networking Apps, Content management tools etc are some areas which can use MongoDB as a data store |
| Performance | Performance is relative and would depend on the schema of the actual data that’s stored. MySQL is generally suited for data structure / relations that are known prior and as a result of this SQL constructs like JOINS, FOREIGN Keys etc can be used. | MongoDB provides better performance at times since there are lesser index lookups and not complex joins |
MySQL And MongoDB Installation
Please follow these tutorials for installing and downloading MySQL and MongoDB.
>> How To Download MySQL For Windows And Mac
>> Install MongoDB On Windows: A Step-By-Step Guide
To get started with MySQL and MongoDB, you can utilize their respective docker containers, which reduce the hassles of individual installation/downloads and reduces the setup to just run/set up the container images.
MySQL And MongoDB Queries
In MySQL, SQL is the query language that’s used to retrieve data from the database table, whereas for Mongo DB a query language describing document objects is required.
Let’s have a look at an example where we will create data for an employee object.
The parameters are as follows:
For MySQL, we will have a table named employee, with fields as follows:
Id – INT
Name – VARCHAR(100),
City – VARCHAR(100),
Age – INT
For MongoDB, since the documents are of JSON type, we don’t need to create/specify a schema, but just start inserting the documents.
The below table shows the step-by-step comparison for queries for both MongoDB and MySQL.
| Query | MySQL | MongoDB |
|---|---|---|
| Create Schema | In MySQL we can create schema or database using CREATE SCHEMA employee | use employee; The above command would create a new schema (if it's not already created) else switch to the existing schema |
| CreateTable | CREATE TABLE employee_details(id INT PRIMARY KEY, age INT, name VARCHAR(100), city VARCHAR(100)); | No specific create script / query required |
| Primary Key | Explicitly mentioned (as part of the create) | By default assigned by the server - which is named as `_id`. For a user defined key - we can create Unique Index, and use the same when data is accessed |
| Creating Index | MySQL by default creates an index on the PRIMARY KEY defined in the table creation script. For additional Indexes, CREATE INDEX command can be used CREATE INDEX index_name on table_name(column_name(s)) | db.employeeDetails.createIndex({"id":1},{unique:true}) |
| Inserting Record | INSERT INTO employee_details values(1,24,'Steve Mitchel','Boston'); | db.employeeDetails.insert({id:1,name:"Steve Mitchel",age:24,city:"Boston"}) |
| Query Record (All) | SELECT * FROM employee_details | db.employeeDetails.find(); |
| Query Single Record | SELECT * FROM employee_details where id=1 //Output  | db.employeeDetails.find({id:1}) //Output  |
| Explain Query | EXPLAIN ANALYZE SELECT * FROM employee_details where id=1 | db.employeeDetails.find({id:1}).explain() |
| List Indexes | SHOW INDEX FROM employee_details; | db.employeeDetails.getIndexes(); |
| Delete record | DELETE FROM employee_details where id=1 | db.employeeDetails.remove({id:1}) |
| Delete table | DROP TABLE employee_details | db.employeeDetails.drop() |
MySQL Vs MongoDB Performance
MongoDB queries can be limited due to the unavailability of JOINs and MongoDB’s capability to handle highly unstructured data, while in terms of speed it’s blazing fast.
MySQL, on the other hand, has data representing more hierarchical relationships which causes the data to be stored as denormalized tables, and then during querying complex operations like JOINS are performed, which makes queries powerful but at the same time more complex and slower.
Frequently Asked Questions
Q #1) Can MongoDB be used for transactional applications?
Answer: MongoDB is better suited for non-transactional applications that have heavy unstructured data. However, transactional consistency is something that MySQL is proven to handle effectively and is better suited for applications having such a requirement.
Q #2) Which is better – MongoDB or SQL?
Answer: Using MySQL vs MongoDB would depend largely on the kind of application that’s being developed. For example, it would be wise to use MySQL over MongoDB, if,
- You need ACID/transaction level compliance. For example, banking applications need transaction compliance, where either all parts of query execute else none of it
- Data being operated on is structured and well defined. MySQL will expect all data to be structured and defined before we can start operating on it. It does provide options to extend/modify the existing tables by adding/removing columns.
Similarly, you can use MongoDB over MySQL if your requirements are
- A loosely coupled schema
- High scalability, where you would need horizontal sharding
- You don’t need transaction-level compliance
Q #3) How to import MySQL database to MongoDB?
Answer: Since the format of the data in MongoDB is JSON and MySQL is row/column, it’s not directly possible to convert/migrate the data and tables. However, there are custom/paid tools and utilities available which can help in migrating data from MySQL to Mongo. One such example is using studio3t.
Conclusion
In this tutorial, we learned about the important differences between MySQL and MongoDB databases. Both the databases are based on different concepts and are suited to different requirements.
MySQL is a relational database and stores data as rows and columns and is generally suited for applications having structured and well-defined data and applications which require high levels of consistency/transactions, etc. One such example can be banking applications.
NoSQL databases like MongoDB are suited for large-scale applications where there is no definite structure/schema defined for data. Most of the social networking platforms like Facebook, Twitter heavily use these databases for storing details like user info, messages, etc, which have a semi-defined structure but the schemas are bound to change over time and require a heavily scalable system.
Suggested reading =>> MariaDB vs MySQL – A comparison