1

I have around 500 tables and each table is for a specific device. every table is getting full with data that comes every 3 minutes. Now I need to store averages of different timeframes of one same column(data) in tables .

timeframes are 10 minutes, 1 hour , 12 hour , 24 hour ,1 month.

Is mysql events good for this purpose? If yes how many of them is required , I mean should I create a table called 10 minute averages with 500 columns with one event or divide them into 5 tables with 100 column and with 5 events? in second case there would be 25 events

If not what is the better solution? as you see these are datacentric jobs.

Thanks a lot.

Tushar
  • 3,527
  • 9
  • 27
  • 49
backend developer
  • 23
  • 5
  • How do you want to use that data? – Ankit Jindal Feb 16 '23 at 04:56
  • what do you mean by using that data? if i understand you right, my website should be able to draw a diagram of them in different timeframes but there are 480 data per day so if i don't save them and wanna draw them whenever there is a request it takes long time @AnkitJindal – backend developer Feb 16 '23 at 05:04
  • What do you want to achieve with an event? It can recalculate your averages periodically and save freshed data into some additional table(s). *how many of them is required , i mean should i create a table ..* You must create one table with ~3 columns (timestamp, source table name, average value). PS. I see nothing which needs in "recurring" in your task. – Akina Feb 16 '23 at 05:29
  • i need previous data too , it shouldn't be updated , for example there is an annual diagram with this 24 hour averages in this case it will have 365 points so i need to store them @Akina – backend developer Feb 16 '23 at 05:51
  • these tasks should be done for example every 10 minutes or 1 hour @Akina – backend developer Feb 16 '23 at 06:00
  • *i need previous data too , it shouldn't be updated* Simply do not remove existing columns from the averages table mentioned above. *for example there is an annual diagram with this 24 hour averages in this case it will have 365 points so i need to store them * Add columns "average period start" and "average period length", what a problem? – Akina Feb 16 '23 at 06:13
  • *these tasks should be done for example every 10 minutes* You may create separate event procedure for each average length period, with separate firing schedule. For example the event which calculates 10-min averages may be fired each 10 min whereas daily average event may be fired daily. Also the event procedure may check for existing averages and do not recalculate existing value if there is no changes in source data. – Akina Feb 16 '23 at 06:16
  • lets consider 10 minutes average, you mean i just need table name, datetime, avg columns , in this case there would be 500*144=72000 data per day in table and for a year it would be 26,280,000 data ,it isn't very bad? for example for searching between them? @Akina – backend developer Feb 16 '23 at 06:34
  • thank you very much for the second comment , i understood it completely @Akina – backend developer Feb 16 '23 at 06:37
  • 26kk rows is average data array. If you create suitable indices then the searching will be fast and easy process. – Akina Feb 16 '23 at 13:21

0 Answers0