I need to communicate something to a programmer but I need to better understand my options first.
The requirement is that totals and statistics be calculated from data being inserted into multiple tables. Basically, we need to look for matches, close matches and other things in order to generate additional data such as totals, stats, etc.
It sounds like using an in-memory table would be the best way of storing the the queried data temporarily in order to generate the stats, then write them to another regular table for access.
At first, the thought was to create a process, say a cron task, which would constantly check the database, do some calculations and figure out if there are any matches. I have to imagine that there must be more efficient ways of getting near instant calculations based on DB entries than using external scripting?
My question is;
Can MySQL run queries on its own instead of using an external cron job to find these matches?
If so, what MySQL functions should I be looking into, familiarizing myself with so that I can know what options I have when speaking with the developer.
What known methods are there of doing this kind of thing instead of using external scripts if MySQL doesn't have such functions?