SQL or NoSQL in 2018: comparison of MySQL and MongoDB
In this post:
- Main differences between relational vs non-relational
- The Scalability
- Structure
- Language
- MongoDB vs MySQL
- MongoDB NoSQL - advantages
- MySQL SQL - advantages
When it comes to selecting prime database for your new project, many questions will arise:
- should I replace my legacy Database?
- a relational RDBMS (SQL) or non-relational (NoSQL) data structure.
- Which vendor to chose:
- MySQL, Oracle, IBM DB2, Microsoft SQL Server, MariaDB, PostgreSQL
- MongoDB, Cassandra, IBM Domino, HBase,
- Select subtype of the DB
- column, document, key-value (map), graph or multi-model
- Distributed relational databases,
As you can see choosing a new DB for your project is not trivial job. Many topics should be considered. Here we will have a look on point relational RDBMS (SQL) or non-relational NoSQL DB.
- NoSQL stands for Not Only SQL and it's using a free model other than the tabular relations.
- SQL (RDBMS) - relays on relational model which consists of tables / relations made of columns and rows, with key identifying uniquely each row.
Main differences between relational vs non-relational
The Scalability and Structure
The biggest difference is that SQL databases are vertically scalable, while NoSQL databases are considered as horizontally scalable. This means that:
- RDBMS performance is managed on server level by increasing his 'power': CPU, RAM, Disk(SSD), network.
- With NoSQL you handle more traffic by adding more servers in your NoSQL database. Which is done without pain of configuration and down time.
You can think of it as abstraction: RDBMS is a huge space telescope vs netword of small telescopes (NoSQL). The first require plan and time for enhancement and the second on you can add or remove new telescope with ease.
That's why NoSQL suits better larger, ever-changing data sets which requires dynamic add of new nodes. Google is using it's one BigData, facebook is using hive, YouTube moves to Google's BigTable( from MySQL)
As we mentioned SQL databases are strict and table(column and row) based.On the other hand NoSQL databases could be:
- column
- document
- key-value (map)
- graph
- multi-model
Language
The next obvious difference that comes to mind is the language used in both. For RDBMS it's used SQL(Structured Query Language) which is very popular language with simple syntax:
SQL (MySQL) syntax:
SELECT * FROM Customers;
SELECT FirstName, LastName FROM Persons;
SELECT * FROM Persons WHERE LastName='John';
SELECT * FROM Customers WHERE FirstName LIKE '%a'
INSERT INTO Customers (LastName) VALUES ('John')
UPDATE Customers SET LastName='JOhnD' WHERE LastName='John'
SQL is very useful and powerful dealing with RDBMS but has some limitations due to relational model. This limitations are solved by NoSQL.
You can play with SQL at this link: SQL Statement Select
NoSQL has a free model and dynamic data structure. Data can be stored in many ways depending on the project needs. It can be graph-based, column or document oriented, map( key value) store. The problems solved by NoSQL are:
- you have more flexibility compared to relational model;
- documents are allowed to have different structure
- new fields can be added dynamically
- model can grow horizontally
- documents can be created without defined structure
NoSQL(MongoDB) syntax:
db.customers.find()
db.customers.find( { "borough": "Manhattan" } )
db.customers.find( { "address.zipcode": "10075" } )
db.customers.find( { "grades.score": { $gt: 30 } } )
db.customers.find( { "cuisine": "Italian", "address.zipcode": "10075" } )
db.customers.insertMany([
{ item: "journal", qty: 25, size: { h: 14, w: 21}, status: "A" },
{ item: "notebook", qty: 50, size: { h: 8.5, w: 11}, status: "A" }
]);
as you can see at first glance language difference is not that huge.
You can play with MongoDB at this link: MongoDB CRUD Operations
MongoDB vs MySQL
MongoDB NoSQL
Advantages of using MongoDB could be:
- Price: MongoDB is free for commercial use under GPU (general public license)
- Flexible: As we mentioned adding new columns and fields on MongoDB is without penalty on application performance or breaking existing data
- Performance: High-performing on basic operations.
- Free schema: data structure can be change without need modifying any of your existing data or any configuration cost.
- Scalability: MongoDB is horizontally scalable, which ease scaling your business according to market needs
- User friendliness: The language is user-friendly in a way that developers can handle most of the queries with ease.
Disadvange of MongoDB
- up-time may require a DevOps team which will cost you money
MySQL SQL
The following are some MySQL benefits and strengths:
- Price: The database is free and open source.
- Community: huge community, many resources, extensive testing, reliable and stable
- OS diverse: MySQL is available on Linux, Windows, Mac.
- Language: You can used it with SQL or with many other languages like: Java, Python, Node.js, Ruby, C# and PHP.
- Scalability: The MySQL can be replicated on many nodes, this gives you control on scalability and load balance
So which Database you are going to chose all depends on your project requirements, team and client needs.