[Facts Alert]When to use which one ?MySQL Vs MongoDB

#sql #database #webdev #nosql

Hello everyone!

In this post I will share about SQL vs NoSQL, most of you are already familiar with SQL database, and have a good knowledge on either MySQL, SQL Server, or other SQL databases. In the last several years, NoSQL database is getting widely adopted to solve various problems.

MongoDB

MongoDB is one of the most popular document-oriented databases under the banner of NoSQL database. It employs the format of key-value pairs, here called document store. Document stores in MongoDB are created is stored in BSON files which are, in fact, a little-modified version of JSON files and hence all JS are supported.It offers greater efficiency and reliability which in turn can meet your storage capacity and speed demands. The schema-free implementation of MongoDB eliminates the prerequisites of defining a fixed structure. These models allow hierarchical relationships representation and facilitate the ability to change the structure of the record.

Pros

MongoDB has a lower latency per query & spends less CPU time per query because it is doing a lot less work (e.g. no joins, transactions). As a result, it can handle a higher load in terms of queries per second.
MongoDB has a faster write speed because it does not have to worry about transactions or rollbacks (and thus does not have to worry about locking).
NoSQL databases are cheap and open source.
NoSQL database support caching in system memory so it increases data output performance.

Cons

MongoDb does not support transactions.
In general, MongoDB creates more work (e.g. more CPU cost) for the client server. For example, to join data one has to issue multiple queries and do the join on the client.
No Stored Procedures in mongo dB (NoSQL database).

MySQL

MySQL is a popular open-source relational database management system (RDBMS) that is developed, distributed and supported by Oracle Corporation. MySQL stores data in tables and uses structured query language (SQL) for database access. It uses Structured Query Language SQL to access and transfer the data and commands such as 'SELECT', 'UPDATE', 'INSERT' and 'DELETE' to manage it.Related information is stored in different tables but the concept of JOIN operations simplifies the process of correlating it and performing queries across multiple tables and minimize the chances of data duplication. It follows the ACID (Atomic, Consistent, Isolated and Durable) model. This means that once a transaction is complete, the data remains consistent and stable on the disc which may include distinct multiple memory locations.

Pros

SQL databases are table based databases.
Data store in rows and columns.
Each row contains a unique instance of data for the categories defined by the columns.
Provide facility primary key, to uniquely identify the rows.

Cons

Users have to scale relational database on powerful servers that are expensive and difficult to handle. To scale relational database, it has to be distributed on to multiple servers. Handling tables across different servers is difficult.
In SQL server\'s data has to fit into tables anyhow. If your data doesn't fit into tables, then you need to design your database structure that will be complex and again difficult to handle.

I have used NoSql Only... as i'm a beginner and there is lot to learn ,here are few insights of using NoSQL
Storing large volumes of data without structure: A NoSQL database doesn\'t limit storable data types. Plus, you can add new types as business needs change.
Using cloud computing and storage: Cloud-based storage is a great solution, but it requires data to be easily spread across multiple servers for scaling. Using affordable hardware on-site for testing and then for production in the cloud is what NoSQL databases are designed for.
Rapid development: If you are developing using modern agile methodologies, a relational database will slow you down. A NoSQL database doesn\'t require the level of preparation typically needed for relational databases.

Would be glad to know what are your reasons for using SQLDB or NoSQL DB.
Comment down below 👇

Top comments (4)

Aaron Reese • Mar 31 '21

The greatest benefit of NoSQL databases is no fixed schema. This is also one of its greatest drawbacks. Data integrity has to be enforced somewhere and the best place for this is the data store. You could do it in the application layer using models and classes but a data store is likely to support more than one application so you now have to keep the distributed models aligned. You also end up writing more code to deal with documents that meet an older version of the model.
NoSQL documents are great when they can be read atomically and contain all the data you need, but access is slow and expensive if you need to do aggregation or go across documents. E.g. a document contains the customer delivery address, order date and a collection of item lines. Great to return the order information to a client portal. Not so great if you need to summarise sales by product group, month and region. You either write all the data to a second document (data duplication) or eat the cost of the aggregation.
One really good use of NoSQL is to quickly write the atomic data away in a structural format for later processing where high volumes might cause locking issues for a RDBMS. An example would be a ticket booking site where concert tickets go on sale at 9am and 50,000 orders are being made concurrently. These orders are the read into the SQL database from a message queue.
Another use is to reverse the process. Extract historic data from expensive live storage into documents and cold storage. You can then delete the historic data from the SQL tables.