MySQL vs NoSQL , which one to use for calculating averages over a very large dataset [closed]

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solici开发者_开发百科t debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 11 years ago.

The problem is that I need to store 1 integer every day (adding new data with time stamp) for lots of entities every day (lets say 10s of million entities, but they could easily be 100 of million or even half a billion as the entities are growing in population)

The entities are grouped in an hierarchy and I can filter using those.

Now I would like a quick way to calculate average of these millions of values over a time period (and perhaps also only calculate average using filtered data). My gut instinct is that as this data grows (potentially 20 millions row every day it will be very hard to guarantee performance.

Can you suggest an easy solution. Should I stick with MySQL or some other fancy NoSQL solution would be better. As it stands it is already painfully slow to just count all the entities (perhaps MySQL needs tuning)

Edit: So when values are retrieved, some values come as null (which mean that nothing can be said about those values). So in a query which spans between Jan 1, 2000 and Jan 10, 2000. If 2 values are null, then for average calculation, 8 data points would be used (and it would be divided by 8 for average calculation, so its not like dividing it by number of days). My concern is that even count() takes s** load of time. Perhaps its about tuning, but I would be interested in some thing which doesn't need tuning and works for this particular problem as well

---

You should probably look into OLAP solutions, where aggregated data is the name of the game. RDBMSs typically don't handle aggregation that well (of course, there are ways to assist it with indexing, partitioning, etc.). But, OLAP cubes are designed to handle large amounts of aggregation and slicing/dicing on the fly.

---

i think either one would do. The trick to calculate a running average is to do it as you insert, not on demand. Averages are easy because its just the sum divided by the number of addends.

You can use database triggers to do it as things get inserted (or updated, or deleted), and with nosql I think the views get updated automagically for you. With a sql solution, you can also do it in your application as part of your service layer.