I'm after some guidance or advice really. We've built an application and its gone mental with custom since coronavirus. We are after some advice for something we have never tackled.
The problem we are having is in fact the job table. We have 1000's of office-based users updating, creating, and editing jobs all day. We also have then 10's of thousands of apps out in the field connecting all-day to this table to read their rotas and then update it if they clock in or out of locations.
We've put in all relevant indexes and we are still starting to see a slowdown. To the point some times where we have to restart MYSQL.
After many hours of Googling, we have come across many solutions for big data including solutions on here but we'd like to ask the community what they think or the following idea, or is there a better way?
As the system grows and we get more clients this is only going to get worse. Being that the apps can only see 1 day back and 7 days forward whereas the rota table stores all of the rota history from the beginning of time we were thinking of creating an exact replica table of the job table. This table would store all rotas from yesterday and forward 7 days using a cronjob to delete older data and add new data each night. The apps would then connect to this table to get the jobs list and update the original table if they clock in or out.
If users update fields for jobs yesterday or within 7 days we would use triggers to keep the two tables in sync or maybe do it through code so it was more accurate?
This way we have taken the read load away from our main rota table by pointing the apps at the new table.
Our question is, is this a good way to go, or are we digging a hole we can't climb out of?
Simply archive off jobs from more than a year ago to another table. If users request to see jobs from over a year ago it simply looks in the other table. While we like this solution we can see its a sliding scale. As we get more customers we will have to start archiving sooner and sooner until the problem reappears in a few year's time.
if these ideas are no good does anybody have any other suggestions we can look at? Partitioning the table is not an option at the minute.