Mysql is relational db with high performance. In SQL we need to define the relations of our data tables. This requires good design, but makes it hard to change the data structure.
Also need to define when adding coloumns. Removing coloumns is even riskier.
In nosql, which stands for Not Only SQL, the data can be loose. Like in json, fields can be added or removed, not restricted by other members in the collection.
For example if we have studends table with the following fields - name, id, phone. In mongo we can add a student with field mobilephone, without the need to modify the existing students. In mysql we need to add a coloumn and define the default value to set for the existing students.
While mongo is more flexible, this is its advantage and disadvantage.
Restrictions makes developement slower, but forces you to think about your data structure and design.
Quick growing agile projects may prefer nosql, while heavy projects, which require high performance probably choose mysql or other relational db.
I think it is important to consider the transaction speed and scaling too in your comparison, where non-relational databases lack in. However I really like MongoDB's JSON like style, I am new to it but I really like it so far.
There are a lot more trade offs that you should look into when deciding on a database. This is something I've dealt with the consequences of more than once.
Consistency, how the db handles transactions, how the db scales, how easily the db handles changes are a few things that you should probably consider when looking at choosing a db. I've hit issues with each in the past.
Currently I use postgres with BDR at clickup.com, and am going to do the same for gitdash.io
We've definitely run into some issues with it, but I think it was a good choice in the end. I've used a few different dbs in production and this has been the best. We can pretty easily migrate the db and add/remove columns to tables as necessary, which is very necessary since we make a lot of pretty large changes. This was an issue in cassandra, we couldn't pivot some of our ideas easily and to use cassandra to its best potential you kind of need a defined schema and query plan from the beginning. Postgres is also super easy to use if you know SQL already.
I've also previously used mongo and couch db in production. Mongos lack of transactions made it a bad choice for my current projects IMO.
Making a db choice is definitely difficult, and I'm no expert in it, but I think if you go with one of the larger relational db names you won't necessarily be making the absolute wrong decision.
Wow nice data