In point of view all of them MySQL,Cassandra and MongoDB are good. There are several ways to achieve best out of them with all of the technologies. It is more a question of how you use them. Your ideal solution may use a combination of these, with some consideration for usage patterns.
Here i am going to discuss about very basic advantages of each of them and finally best example of their usages.
NoSQL solutions like MongoDB and Cassandra they are very hot topic in market because they have solve very big problem of frequent insert operations. both of them get a lot of attention for their insert performance. People tend to complain about the update/insert performance of relational databases like (MySQL) but there are ways to mitigate these issues.
We will start with MySQL, O’Reilly’s High Performance MySQL it is very good book for making fast and optimize your MySQL. Here in this book they have mentioned number of ways to tune your MySQL as per your requirements.
Like:
1. Optimise the schema
2. MyISM and InnoDB, MySQL gives you great flexibility to choose engine type of your table
3. Distribute your DB, if required then use partition and shard data
4. Queue ur inserts and updates at the application level before insertion into the database
Now lets see about MongoDB and Cassandra:
Both MongoDB and Cassandra databases perform well on reads where the hot data set fits in memory. Both also emphasize on join-less data models (and encourage denormalization instead of normalize DB), and both provide indexes on documents or rows, although MongoDB’s indexes are currently more flexible.
Cassandra’s storage engine provides constant-time writes no matter how big your data set grows. Writes are more problematic in MongoDB, partly because of the b-tree based storage engine, but more because of the per database write lock.
Cassandra offers the organization of a traditional RDBMS table layout combined with the flexibility and power of no stringent
structure requirements. This allows data to be dynamically stored as needed without performance penalty for changes that occur. In addition, Cassandra can store structured, semi-structured, and unstructured data.
Cassandra far exceeds other systems on write performance due to its append-only commit log while always ensuring durability. Users must no
longer trade off durability to keep up with immense write streams. Data is absolutely safe in Cassandra; data loss is not possible.
MongoDB is best suitable to store unstructured data. And this can organize your data into document format. It is very useful for applications that scales massively and require faster data access from these big data stores.
MongoDB is known for Flexibility in schema design (JSON type document store) and Scalability – Just add up nodes and it can scale horizontally quite well.
For analytics, MongoDB provides a custom map/reduce implementation, If you’re looking at a single server, MongoDB is probably a better fit.
Cassandra provides native Hadoop support, including for Hive (a SQL data warehouse built on Hadoop map/reduce) and Pig (a Hadoop-specific analysis language that many think is a better fit for map/reduce workloads than SQL).For those more concerned about scaling, Cassandra’s no-single-point-of-failure architecture will be easier to set up and more reliable. Cassandra also gives a lot more control over how your replication works.
MySQL: he is all time winner for all kind Web applications.
MongoDB: It is best suited for all kind of logging and analysis job, very easy to write map reduce.
Cassandra: best suited for very fast insertion and dynamic data where run time new field can be add without downtime. Like build resume kind of applications.