I've got a backend where I have 100s of users, who are actively using a system. This system uses quota and each user has a quota e.g. 500 /month or 2000/year.
Whenever a user subscribes to our service his period start, so each user has a different start date for a period.
We maintain usage in a usage table, but this is a table that's not aggregated.
A couple of times an hour we run queries to determine if a user went over his quota or not. Due to the amount of users this script takes quite a bit of time and this hogs the mysql server.
We need someone with expert mysql skills to come up with a better way of doing this.
UPDATE - I found the cause of the problem - it was not related to this description, but to another query in the script, which took 28seconds to complete. After adding an index to a table that problem was fixed.
The new requirement for this project is therefore to do a general analysis/improvement on my mysql setup. It now runs well again, but I want to make sure we make any improvements that can be made. SO if you have generic mysql performance tuning experience I invite you to bid.
22 freelancers κάνουν προσφορές με μέσο όρο $55 γι' αυτή τη δουλειά
Hello, Your system should be built in such a way that you should not need to check couple times every hour, only when the user accesses your system. Best regards, AccuPro.