DEV Community

alan2001-d
alan2001-d

Posted on

Most performant DB for CRUD app

I'm looking into creating a CRUD app that handles around ~100k of rows which can go bigger and has some API Calls that runs on that data. I will be doing many SELECT/UPDATE/DELETE/INSERT over this data which contain webpages text that are long.

Also there would be there another table for logs which gonna be big (has long text of around 300-400 character).

When to consider using MySQL over MongoDB or Elasticsearch and vice versa, I searched on google and saw benchmarks showing that MySQL is faster than MongoDB in select/update/insert/delete queries (1k/10k/100k rows) which is kinda confusing as MongoDB is being stated that its better when data grows bigger, I haven't used MongoDB or Elasticsearch before just MySQL.

I would appreciate your help so much.

Benchmark

Top comments (3)

Collapse
 
cjbrooks12 profile image
Casey Brooks

All 3 databases you mentioned have completely different purposes. None of them are interchangeable with each other.

  • MySQL (and other relational databases) are your most efficient databases. That's why developers have been using them for 50 years, and why we'll be using them far into the future. Most data is relational (a user has associated blog posts, those posts have tags, etc.), and relational databases are exceptionally good at handling this kind of data. GitHub itself uses MySQL as the store for its user data, so you won't have issues with scale or performance there. If you do, it's a problem with your code or ORM, not the database itself.
  • MongoDB (and other document databases) are unstructured, which has both benefits and drawbacks. Because it does not enforce any schema, it is not able to optimize queries in the way that a relational database can. Trying to map relational data into a document database is going to cause problems, especially as you might not really notice the relational data until it's too late. A lot of developers (myself included) were badly burned by MongoDB a few years back because of their excellent marketing campaigns. But the truth is that MongoDB should not be the first choice of database. It was designed to be a tool for massive amounts of unstructured data you will later process and transform to get in the shape you want. It's a tool for analyzing data in a data warehouse, not a tool for managing normal business/CRUD applications (despite what their marketing tells you).
  • Similar to document databases, ElasticSearch is technically a database, but is highly optimized for search. It holds on to data that you will use for searching through large datasets, but is not designed for general CRUD-like data.

As for the performance benchmark you linked to, you might not have noticed that the queried it's running are all fairly trivial. A simple insert into a single table, and a fetch from a single table. If you were to add any kind of join to those queries, MySQL would start to be significantly faster than Mongo.

In addition, it sounds like you're falling into the trap of premature optimization. While performance is important, you likely won't notice these kind of subtle differences in performance, and it's not worth chasing the latest tools because of some supposed performance benefits. Just stick with what you're comfortable with, what you like using. And know that there is never a single best tool for the job, there's just good tools that will all work for your project, and performance really shouldn't be a motivating factor for most applications. Unless you're doing something like writing a database, or doing video game development where every frame and millisecond matters, there are much more important things to consider than just performance.

Some comments may only be visible to logged-in visitors. Sign in to view all comments.