Big statistics from tables. How to correctly read the data in the database, how do you do it?

Question

MySQL

What is the right way to design statistics for something?
Is it possible / necessary to read the database? Or is it better that all the statistics should already be processed, and just lie in the database?
I mean all sorts of COUNT, AVG, SUM, and so on, on the database side. Or do pre-processing immediately upon receipt of data?
Will it be adequate to consider all this by the forces of the database itself in real time, or is this a stupid approach?
Let's say we have to work with 3 tables (Joins + subquery), which display the result in 100 rows, and each row has 10 columns. All 10 columns are the data of aggregation operators.
The tables themselves contain up to 100k rows of data each.

Answer 1

It all depends on the specific case, there is no universal answer to your question. As a person closer to the administration, I can say that on volumes of hundreds of thousands of rows, that is, they fit perfectly in RAM, it is quite possible not to bother with premature optimization, the main thing is that the tables have the necessary indexes.

Answer 2

Create Event
Or, periodically, through cron and microservice: we do independent intermediate calculations - asynchronously, then - the final one from the intermediate ones.

Answer 3

clickhouse

Answer 4

It all depends on the specific task.
Some statistics are considered immediately if they are performed rarely and / or quickly.
If the statistics are difficult to calculate, but their relevance is daily, then the time of the least activity of users is selected. We have it at 4 am in the current region (+ even less on weekends). At this time, heavy statistics are launched and considered. The results are sent to a separate table, from where this data is then quickly taken. Or an html-page with a report is immediately prepared.
Approximately the same if the relevance is in a few hours or minutes.
100k rows in a table is nothing at all. Tiny plate. It all depends on the request itself, how it is written.