I am having the following problem processing big data in the database:
Basically all the metering from digital sensors are stored in the database for each second. What the reports should show from all that data is only the occurred changes, for example at time X the register #1 changed value from 0 to 1.
I have created a procedure that is able to return only the data i need (the changes), and that is saving me a lot of processing in php BUT the big problem is that for a current data of 4 days the query takes 6 * N seconds to complete where N is the number of the selected registers.
Now i was wondering what is the best solution to overcome this problem.
Another thought is to make a trigger on each new insert of the data metering but the problem is that this will be more complicated since i will need to look into the previous metering that were submitted at another time.
So I thought to create views that will be automatically updated when new data arrives in some way. That means when the request is made for the reports the data will be ready and fetched from the view.
Will this be a good solution?