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.
projectsAsked:
Who is Participating?
 
skijCommented:
MySQL works best with exact matches, however, near matches and flexible criteria can be achieved with extra work.  

Pattern Matching
https://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

Regular Expressions
https://dev.mysql.com/doc/refman/5.1/en/regexp.html

Soundex
https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_soundex

Sounds Like
https://dev.mysql.com/doc/refman/5.1/en/string-functions.html#operator_sounds-like

Additionally, you could create specific functions to achieve  much more, including artificial intelligence and Levenshtein Distance:
https://dev.mysql.com/doc/refman/5.0/en/create-function.html
0
 
skijCommented:
0
 
Loganathan NatarajanLAMP DeveloperCommented:
Can MySQL run queries on its own instead of using an external cron job to find these matches?

Open in new window


Yes. Mysql has similar cron auto execution of queries.

More details here, https://dev.mysql.com/doc/refman/5.1/en/events.html
0
 
projectsAuthor Commented:
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?
0
 
projectsAuthor Commented:
I had no idea where to start but now have plenty of information. Thank you for the many good leads.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.