Schema design for metered billing depending on account usage
I regularly handle tables with 250GB of data that needs "counting" (Example: https://phabricator.wikimedia.org/T124676).

Let me start by saying that MySQL is not a good place for these kind of queries- its focus is OLTP (in a nutshell, short reads and writes in parallel), not OLAP. There are multiple storage/computing models (such as column-based data stores, or datawarehouse solutions that allows map/reduce jobs in parallel) that would be better suiting for your task, so if this is a business-critical/very frequent part of your needs, I would suggest setting up a complementary service that allows easier/faster handling of these kind of tasks. The reason is that counting things on MySQL usually require "snapshoting" and reading all rows one by one, and that can only be done on a single thread.

Having said that, your needs may not merit a full-blown solution, and only make that specific need faster. You can implement analytics by implementing such functionality on regular MySQL objects. The implementation will depend on your requirements, and that is mainly: "freshness" of your data, its dynamic nature, concurrency and if it is append-only.


If your counter does not need to be 100% accurate, you can run counting jobs every X time. Make sure no more than 1 job is running at each time by locking on a particular resource. We do such things with special pages such as: https://en.wikipedia.org/wiki/Special:WantedPages
If data is not always modified, but it needs to be more or less up to date with the rows, you can start a background thread that gets notified when data changes. If it gets notified several times before it runs, it de-duplicates the job by updating it only once. We do such a thing for Category updates: https://en.wikipedia.org/wiki/Category:Contents
Both of the "counters" from the previous tables are stored on auxiliary tables.
If you need to have perfect counting, you need to cache it with a trigger-like execution. It doesn't need to be a on server side, but be part of the transaction that updates the row. This will be more costly, as it will increase the latency of each individual DML, but it may be interesting in some cases. In our model, user have (or at least, used to have) an edit counter that it is transactionally updated every time an edit is created or deleted.
If your data is append-only, you can avoid counting all data every time, and only count the most recent records. Store the last counted record and then the total/per month/yearly/etc. count on another column, per item. We use such a model for counting things like number of visits per article.


In general, with MySQL you are forced to count- whether you cache or precache that value is a question of handling caches, which is a very common problem (although as you may know, not a simple one).

One common mistake is to store a single counter field when there is a lot of concurrency- e.g. visits for a webpage. For such cases you can distribute your counters according to your structure (e.g. one per fronted) or just use some kind of hashing table so you do not have to serialize every single update. On actual counting, you can sum the much fewer counters, which will be easier than counting the individual values.

If you need to speed up counting, there are even frameworks such as Shard-query that could simulate the panellization of queries for MySQL to some extent.

The summary is that you have 3 options: make things consistent, make them fast or make them cheap, choose 2 :-)