DEV Community

Comparing MongoDB & MySQL

Jignesh Solanki on December 05, 2017

Imagine finding a DBMS that aligns with tech goals of your organization. Pretty exciting, right? Relational databases held the lead for quite a ti...
Collapse
 
nerzhul profile image
Loïc Blot

Can we have your queries, not just a invalid bench without data ?

Also note, not tuning a SQL engine and comparing with MongoDB which doesn't require real tuning is not valid, you need to tune the MySQL/PostgreSQL buffer for reading & writing performance, especially on a such AWS instance which has many RAM.

PostgreSQL isn't benched and is far far more better than MySQL & PostgreSQL in terms of features, robustness and security (yes PostgreSQL has IPv6, enabled SSL by default without being crazy & CRAM authentication).

PostgreSQL json backend is faster than MongoDB for unrelational data, see enterprisedb.com/node/3441

Another point, there is no transactions with NoSQL, especially mongodb, your data can be totally broken, and the upgrade scheme after using a NoSQL during years, which moving data scheme can make your apps crazy, because you have to handle all cases, or suffer from crazy bugs.

We also compared PostgreSQL 9.6 with MongoDB 3.2 to bench new huge application we developped for production (~15 SQL hard relational tables with big constraints) and the performance/CPU cost is 1.5 lower using PostgreSQL mixed relational & JSON backend than MongoDB.

Last, a reality we see in our production, MongoDB is not safe, it doesn't ensure data is correctly written, and some data can be lost on huge workload whereas relational data really ensure data is here. The mongodb replication is not really safe, it's the major risk factor.

Collapse
 
xowap profile image
Rémy 🤖

That is an interesting comparison of NoSQL databases.

I don't understand though, both of them have quite flaky but existing nonetheless JOIN implementations. MySQL has SQL-inspired JOIN clauses while MongoDB has $lookup, right?

Collapse
 
bgadrian profile image
Adrian B.G.

Yes, Mongodb has a join since 3.2, but do not take it lightly, it's not a 1:1 replacement. The feature is part of the aggregation framework, you will not want to aggregate data for each user request, it's not meant to be used like this because it needs more resources to return the result.
To avoid these you usually make subdocuments and keep the data in a single big object so you do not require a join.
If your data can be easily put in an excel like table then you shortly use a relationship DB.

Collapse
 
xowap profile image
Rémy 🤖

My motto is: if you can put it in a table then put it in PostgreSQL. If you can't, then put it in PostgreSQL anyways, it has indexed JSON fields after all.

Thread Thread
 
hassan14_pk profile image
Hassan

I guess my line would be to 'if you can put it in NoSQL then do that' and I come from SQL and Oracle.

Collapse
 
xowap profile image
Rémy 🤖

Interesting to know that $lookup is heavy though, I did use it without noticing performance issues but that's still something to consider.

Thread Thread
 
bgadrian profile image
Adrian B.G.

The lookup alone I don't think adds roo much, but usually you need other ops.
And try the test on a large db, eventually with sharding then you'll see the out of memory issue 😀

Thread Thread
 
xowap profile image
Rémy 🤖

large remains to be defined but yup I see the idea

Collapse
 
gpasq profile image
Greg Pasquariello

"To avoid these you usually make subdocuments and keep the data in a single big object so you do not require a join. "

That's a sure-fire way to make your database unusable down the road.

Collapse
 
bgadrian profile image
Adrian B.G. • Edited

A nice article as an intro but ...

Readers, keep in mind you should ignore the VS compares,they are not 2 excluding or replacing technologies, they are usually used in the same project for different data types. Other storages are keyvalue like redis, object storage like Amazon S3, file storage and Graph databases. Other times you'll need the entire history and use an event sourcing DB.

Do not try to squeeze all your requirement into a single bucket, use them all if needed,based on each system module needs.

As for large databases I suggest to read about the new cloud dbs like

  • dynamodb
  • Dybamodb accelerator
  • Aws aurora
  • Cloud bigtable
  • Cloud spanner
Collapse
 
databasesponge profile image
MetaDave 🇪🇺

They're very different kinds of storage system -- I think the bottom line on these things is that if you're relying on simple X vs Y guidance to choose your storage layer then you're honestly not competent to make the choice.

Collapse
 
tonnoz profile image
tonnoz

Nice post, I recently hosted a meetup on SpringBoot and MongoDB where I discussed similar arguments.
In it I touch topics like Comparison with SQL, Schema Design, Performance improvements & GeoSpatial indexes/queries. It is based on M101J from MongoDB University.

Give it a look here: slides.com/tonnoz/bloggo

It includes exercises on Github too. It may be a good start point for who used Mongo superficially or only with ODM.

Collapse
 
josehidrom profile image
Jose Hidalgo • Edited

Almost 47 seconds in query sounds pretty much for me in comparation with 4 secs from MySql. In a operational system maybe it worths a try Mongo but for informational systems, when you have much more than 1MM of registers it really is a bad idea to choose MongoDB.

Collapse
 
grobolom profile image
Vasja Volin

I'm concerned by the misleading chart used in the header. The rankings are given in logarithmic scale, for some reason, making Mongo's adoption seem closer to MySQL's than it really is.

Collapse
 
robbert_stephenson638_85 profile image
robbert. stephenson638

When choosing from MongoDB vs MySQL, it ultimately depends on your project's needs:

MySQL is ideal for structured data, where schema consistency and relationships are critical. It's reliable, with robust performance, and is great for complex queries and transactions.

MongoDB excels with unstructured or semi-structured data, offering flexibility in schema design. It's better suited for handling large volumes of data and allows for faster development when the data structure isn't rigid.

Both have their strengths, so your choice should align with your data's structure and your scalability requirements.

Collapse
 
thomasbnt profile image
Thomas Bnt ☕

Thx !

Collapse
 
ddgcd123 profile image
晴天🏃🏃🏃

MySql query is still much faster than MongoDB

Collapse
 
gpasq profile image
Greg Pasquariello • Edited

Not even close. Lots of benchmarks on google show Mongo hugely faster than MySql in both queries and inserts, unless you're using simplistic myisam. Here's one:

github.com/webcaetano/mongo-mysql