Best practice on retrieving analysis data from multiple query?

I have to run multiple query to get different output and show to the user. For example, total_user, total_sale, average_engagement, total_product, and etc.

I have to filter the data and do comparison maybe in daily, weekly, monthly and yearly. I am thinking which is the suitable way to get it done.

I would like to create an analysis table to store these data, so i will have daily data stored in there. And just query based on the filter selected.

How do you guys handle dashboard data or report data in real case? Will you all query it all over again or create a table to store raw data just for reading purpose?

Sinnbeck

4 years ago

Level 102

I do something similar. I get the data i need each midnight and store them for easy access. I then just have a few queries to extract and do the calculations directly from the database

Be sure to just save the raw counts you need and the calculated results. Mysql can do the calculations on the fly, very fast

1 like

Jeffxy

4 years ago

Level 1

@Sinnbeck Alright, and also do we need to apply partition on the table?

Tray2

4 years ago

Level 73

@Jeffxy Partions is not something you need to apply until the table gets really big and you need to speed things up.

Tray2

4 years ago

Level 73

There are many ways to do this.

If you only use old data that never changes you can use a database view instead of storing the information in multiple places. If it changes all the time and you need a snapshot I'd go with the technique that @sinnbeck described.

1 like

Please or to participate in this conversation.