projects
asked on
Can MySQL do internal queries/calculations?
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?
Thanks.
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?
Thanks.
Can MySQL run queries on its own instead of using an external cron job to find these matches?
Yes. Mysql has similar cron auto execution of queries.
More details here, https://dev.mysql.com/doc/refman/5.1/en/events.html
ASKER
So both the MySQL 'event scheduler' and 'trigger' functions are what I am looking for.
Can this function in terms of match and close matches as well? In other words, are the MySQL functions rather basic or flexible and with some intelligence?
Can this function in terms of match and close matches as well? In other words, are the MySQL functions rather basic or flexible and with some intelligence?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had no idea where to start but now have plenty of information. Thank you for the many good leads.
http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html
http://www.w3resource.com/mysql/mysql-triggers.php