title: Using MongoDB vs MySQL with lots of JSON fields?
body: <p>There is a microblogging type of application. Two main basic database stores zeroed upon are:
  MySQL or MongoDB.</p>
  
  <p>I am planning to denormalize lot of data I.e. A vote done on a post is stored in a voting table, also a count is incremented in the main posts table. There are other actions involved with the post too (e.g. Like, vote down). </p>
  
  <p>If I use MySQL, some of the data better suits as JSON than fixed schema, for faster lookups.</p>
  
  <p>E.g.</p>
  
  <pre><code>POST_ID   |  activity_data
  
  213423424 | { 'likes': {'count':213,'recent_likers' :
               ['john','jack',..fixed list of recent N users]} , 'smiles' : 
               {'count':345,'recent_smilers' :
               ['mary','jack',..fixed list of recent N users]}  }
  </code></pre>
  
  <p>There are other components of the application as well, where usage of JSON is being proposed.
  So, to update a JSON field, the sequence is:</p>
  
  <ol>
  <li><p>Read the JSON in python script.</p></li>
  <li><p>Update the JSON</p></li>
  <li><p>Store the JSON back into MySQL.</p></li>
  </ol>
  
  <p>It would have been single operation in MongoDB with atomic operations like <code>$push</code>,<code>$inc</code>,<code>$pull</code> etc. Also
  document structure of MongoDB suits my data well.</p>
  
  <p>My considerations while choosing the data store.</p>
  
  <p>Regarding MySQL:</p>
  
  <ol>
  <li>Stable and familiar.</li>
  <li>Backup and restore is easy.</li>
  <li>Some future schema changes can be avoided using some fields as schemaless JSON.</li>
  <li>May have to use layer of memcached early.</li>
  <li>JSON blobs will be static in some tables like main Posts, however will be updated alot in some other tables like Post votes and likes.</li>
  </ol>
  
  <p>Regarding MongoDB:</p>
  
  <ol>
  <li>Better suited to store schema less data as documents.</li>
  <li>Caching might be avoided till a later stage.</li>
  <li>Sometimes the app may become write intensive, MongoDB can perform better at those points where unsafe writes are not an issue.</li>
  <li>Not sure about stability and reliability.</li>
  <li>Not sure about how easy is it to backup and restore.</li>
  </ol>
  
  <p>Questions:</p>
  
  <ol>
  <li>Shall we chose MongoDB if half of data is schemaless, and is being stored as JSON if using MySQL?</li>
  <li><p>Some of the data like main posts is critical, so it will be saved using safe writes, the counters etc
  will be saved using unsafe writes. Is this policy based on importance of data, and write intensiveness correct? </p></li>
  <li><p>How easy is it to monitor, backup and restore MongoDB as compared to MySQL? We need to plan periodic backups ( say daily ), and restore them with ease in case of disaster. What are the best options I have with MongoDB to make it a safe bet for the application.</p></li>
  </ol>
  
  <p>Stability, backup, snapshots, restoring, wider adoption I.e.database durability are the reasons pointing me
  to use MySQL as RDBMS+NoSql even though a NoSQL document storage could serve my purpose better.</p>
  
  <p>Please focus your views on the choice between MySQL and MongoDB considering the database design I have in mind. I know there could be better ways to plan database design with either RDBMS or MongoDB documents. But that is not the current focus of my question.</p>
  
  <p><strong>UPDATE</strong> : From MySQL 5.7 onwards, <strong>MySQL supports a rich native JSON datatype</strong> which provides data flexibility as well as rich JSON querying.</p>
  
  <p><a href="https://dev.mysql.com/doc/refman/5.7/en/json.html" rel="noreferrer">https://dev.mysql.com/doc/refman/5.7/en/json.html</a></p>