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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
projectsAuthor Commented:
I had no idea where to start but now have plenty of information. Thank you for the many good leads.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.