Best programming language to run MySQL queries with?

Hello everyone,

I have a bunch of data in a MySQL database.  I want to analyze and get statistics for all the rows.  The queries will take long to complete and intense on the database.

The application uses PHP, but I am curious if there is a better way to run the queries than through a PHP script.  Is it better to run it from a file in Linux using C/C++ or is the R programming good for this since it is for stats.  Just curious.  thanks!
Who is Participating?
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.

Thank you for your question. If I may ask what is your intended use for the data stored in your MySQL database. The best way to answer this question, is to first determine what and how you plan to use this data. If your intentions are to use the data in an online scenario. I look forward to your response.

Best Regards,

parlaysAuthor Commented:
Thanks for the response.

I want to store the compiled statistics in the database and display them online.

I plan to use PHP to display the data but I'm a little concerned about using it to retrieve and compile the data.   Part of the stats will be found through SQL queries alone and the other part of the stats will be compiled from pulling records out of the database and then having the programming language analyze and create the stats.
Since the queries will take long time to run you can use stored procedures to extract the data and then pass it to php for processing.

I would recommend PHP as the programming language and Stored Procedures for processing and retrieving the data.
PHP is probably just as reasonable a choice as any other language. Your best bang for the
buck to minimize disruption to the tables being accessed by your batch program, is to do
the following:
-  Run it as a scheduled job when few users are on your system
-  Use InnoDB instead of MyISAM engine for your tables to minimize locking issues.
   (MyISAM locks entire tables in a search, InnoDB only rows being accessed)
-  Make sure to have indexes to ensure your searches are as optimal as possible.
   In particular make sure that fields used in a JOIN clause are indexed. Also fields your
   WHERE clauses should likely be indexed.

Scott MadeiraCommented:
From your description the gating factor is the time for the SQL to execute, not the analysis of the data once the queries are executed.  If that is the case then I would use whatever language you are most comfortable with.  If there is already PHP involved for other purposes then do your analysis in PHP.  

The only thing that may change that is based on what statistics you are creating.  If you need a high-powered statistical package to create the information you are looking for then I woudl drive your language decision off of what works best with your stats package.  If you have a PHP-based statistics library then use PHP.  If you are using a stats package that has a .NET interface defined for it then use C#.

Use what will work best with your most restrictive part of the project.

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