Pros/cons of MongoDB or MySQL for this purpose [closed]

Closed. This question needs to be more focused. It is not currently accepting answers.

---

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 5 years ago.

Improve this question

I'm looking for a bit of help or guidance on which database to use for a project. If you can raise any points, or note flaws, answer any questions or promote either database type for the purpose I'm about to spell out, I would really appreciate it.

Anyways:

• We have some software that tracks forms.

• We have users that can have MANY different properties, literally hundreds of settings, and I'm not a fan of MySQL tables that wide. I really like Mongo for this.

• We have different types of forms, each can have completely different fields. Right now, we have a list of forms with generic data, then join the relevant table for additional data. I would have all of these fields in one distinct document with Mongo, and I could easily add fields without worrying.

• We have fees, notes, history on each form. I like how in MySQL they are in a different table, and I can get history by form or by user - same as notes.

• Our policy is pretty much keep ALL data, even deleted or pre-edited data... forever. Should I be worried about hitting a size limit? We're probably talking 100gb by the end of 2013

• How many Mongo queries per page will bog things down? 20? 100? Would that change if I had a SSD in the server? (Right now, we have about 60 MySQL queries a page. This can be improved on.)

• Is it a bad idea for my first Mongo project to be a somewhat major bit of software? Is it something I can learn as I go?

• I like the case insensitivity of MySQL column names for quick and dirty things.

• In MySQL, I break things out to different tables. Is it fine, in Mongo, to put data together that CAN be separated? Example: username, email, phone, license1 => [num,isValid], license2 => [num, isValid], notifications => [notification1...notification50000], password hash, salt, setting1, setting2...setting1000, permission1, permission2...permission1000 Of course, I'd make use of the nested style to organize, but is it best to store all this under "user" or break it out to settings, licenses, permissions? Second example: formName, address, notes => [note1 => [user,note,da开发者_JS百科te], note2 => [user,note,date]]

• Is there any problems with doing a HYBRID setup, where user data is is Mongo, and form data is in MySQL?

• We have to run a lot of reports, are there limitations on this in Mongo? For example, would I run into problems looking for every form from the past 40 days with a fee over $10, with the fees in each row totaled up, sorted by the age of the user who filled it out?

• Data redundancy - On the Amazon cloud, MySQL has MASSIVE amounts of redundancy. Is there any service to match that with Mongo? Is it complex to get into setting that up on my own?

• Is MongoDB supported by any "cloud" providers? AWS does a lot for MySQL, but it looks like I'd be on my own for Mongo

Just a few things off the top of my head - I really do appreciate anything anyone has to say.

---

We have users that can have MANY different properties, literally hundreds of settings, and I'm not a fan of MySQL tables that wide. I really like Mongo for this.

We have different types of forms, each can have completely different fields. Right now, we have a list of forms with generic data, then join the relevant table for additional data. I would have all of these fields in one distinct document with Mongo, and I could easily add fields without worrying.

From your post i understand that your ultimate aim is to handle the users & forms that contains varying schema(aka schemaless). I believe mongodb is a right choice for this purpose.

We have fees, notes, history on each form. I like how in MySQL they are in a different table, and I can get history by form or by user - same as notes.

No problem, You can use different documents (or embedded documents based on the size of it - 16 mb is the max size of the doc) to handle this without any problems. so you can have the schema like

  Form
   - form field1
   - form field1
   - id of the fees doc
   - id of the notes doc
   - id of the history doc

or (for embedded docs)

  Form
   - form field1
   - form field2
   - embedded fees doc
             - fees field1 
             - fees field2
   - embedded notes doc
             - notes field1 
             - notes field2

Our policy is pretty much keep ALL data, even deleted or pre-edited data... forever. >Should I be worried about hitting a size limit? We're probably talking 100gb by the end of >2013

You will store as much as data you would do, already there are production deployments storing data over Terabytes.

Is it a bad idea for my first Mongo project to be a somewhat major bit of software? Is it something I can learn as I go?

Yes if you are going to use mongodb without prototyping your application model. i would recommend to implement (prototype) a minimal set of your app (like features that sucks in mysql) and learn basics and see how comfortable you are.

I like the case insensitivity of MySQL column names for quick and dirty things.

Mongo enforces the case sensitivity, because thats a nature of BSON (as well JSON) key value pairs.

In MySQL, I break things out to different tables. Is it fine, in Mongo, to put data together that CAN be separated? Example: username, email, phone, license1 => [num,isValid],

Main advantage of mongo over other sql data store is, you can store as much of relevant info within the same document (within the 16 mb size) . If you are unsure about the size or certain parts of data are growing, then you can split the part into another. Since you are concern about the no of queries, it will drastically reduce the number of requests.

Is there any problems with doing a HYBRID setup, where user data is is Mongo, and form data is in MySQL?

No absolutely not, in fact i am currently running mongodb along with mysql(for transactions alone). But if you are not handling any transactions, you can stick with mongodb.

We have to run a lot of reports, are there limitations on this in Mongo? For example, would I run into problems looking for every form from the past 40 days with a fee over $10, with the fees in each row totaled up, sorted by the age of the user who filled it out?

No i don't see any limitation in this. In fact its very fast handling queries with the proper indexes. But there are certain things you can't do with mongo like normal joins, instead you can use map/reduce to handle the data for reports.

Is MongoDB supported by any "cloud" providers? AWS does a lot for MySQL, but it looks like I'd be on my own for Mongo

Mongohq,Mongolab are some of the dedicated managed mongo hosting services available. Also redhat openshift & vmware cloundfoundry provides the hosting platforms for mongo, you can check out the mongo hosting center for more info

Hope this helps

---

You could use either MongoDB or MySQL for what you are wanting. The main thing to be aware of is scaling. In MySQL you scale vertically. You get a bigger machine, a better machine. And hope it makes a difference. In MongoDB you scale horizontally. You have multiple machines and shard. Scaling vertically has a limit. But scaling horizontally does not. In terms of cost scaling vertically is easy to understand. Scaling horizontally usually results in buying a cluster of machines, and then when you want to scale further it becomes exponential. So it's something you have to consider.

Doing statistical queries is a downside of MongoDB. For a few reasons. First of all there will be features of MySQL you just won't have in MongoDB. Secondly, for anyone who is more of a DB person and is super-familiar with SQL statements they may have a really hard time adjusting to the syntax of MongoDB. It's something new to learn. And people often like (and work well with) what they know.

Like most other 'NoSQL' platforms MongoDB does not employ ACID, which gives it a bit of a performance boost. But that does mean that it may be riskier.

There are some cloud-based solutions. Take a look at MongoHQ and MongoLab. I may be wrong, but I don't believe they have SSD. It's all spindles. But ping their support. They usually reply fast.

In my experience MongoDB does go fast. Very fast. MySQL is slow when you have large tables, joins, etc. And you can index in MongoDB, as you would expect. I've seen that if you index too many things, or things like arrays where it must index each element, then it can be more taxing per transaction.

I would not push you in either direction. It's something that takes some research. I wouldn't say using MongoDB is a bad idea for such a large project, but it would take time to figure out if it works for your situation. As with all things.

There are some alternatives, specifically proprietary extensions to MySQL that can give you a big performance boost (depending on your setup, average type of transactions, etc). One that comes to mind is InfoBright, but these are often costly.

---

Here's some information on MongoDB in the cloud: http://www.mongodb.org/display/DOCS/Hosting+Center