finding query from the MYSQL SP

Dear all,

  right now we have conflict here that developer is arguing where to write the SQL script, right now they are all doing teh SQL script inside PHP code as someone argue that it make the script/query easiler to find in case of problem of slow.

  But if all SQL query writen in store procedure (SP) itself, then once running in the trouble, it is hard to find out the mySQL script that write inside the SP. But doing the code in SP will give faster SQL result, right?

  Please share your experience on how to get rigt of slow in perofmrance of the MySQL in Applicatoin tier and the store procedure in the MySQL tier.

 Also any tools to found out if the MYSQL is the performance bottleneck.

marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
Slick812Connect With a Mentor Commented:
greetings  marrowyung, , ,  many have found some speedup (faster) data retrieval by using stored procedure for some (but not all) Table data sets, but no matter what you use "PHP code" or  "stored procedure", , in Table access speed, I have found that correct and efficient column INDEXS do more to speed things up, than in stored or not. see these MySQL pages -
arnoldConnect With a Mentor Commented:
The difference between SP and code within PHP is the control over the flow of data.
SP you provide it a set of inputs and it provides a previously defined response.

With the code being within PHP you control the flow within the code based on the information you retrieve.
i.e. a daily report of all transactions, it might be better to have it in an SP.

The issue often deals with the complexity of the data manipulation that is required and whether it is better for performance/responsiveness to have the data crunched manipulated within the mysql server scope (i.e. some data reformating/data type variances might not be possible) or provide the response to PHP and process the data there.

phpmyadmin is a good tool that provides info on mysql usage and suggestion on improvement of performance using mysql generated/collected statistics.
marrowyungSenior Technical architecture (Data)Author Commented:
Right now the problem is, we don't know where the bottleneck is and from DBA point of view( I come from MS SQL background) that we should do all data programming in SP, then the script will be compile before execution, then much better.

The data transfer between network is then smaller, then therefore faster!

so if the Query is writen in SP in MySQL, there are no way for us to find it, right? we want to know which SP they come from as the new developer might pick up job from the old developer who was left.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

There is a dismissing return for trying to create an SPfor every combination and every variant of possible data extraction.

In it is difficult to assign a value to on approach versus another in the abstract.

I.e. you have a set of table where you want data based on column1, another deals with column2.
marrowyungSenior Technical architecture (Data)Author Commented:

This probably the case of MySQL, but for MS SQL, it is true that using SP is faster is everything is pre compiled. network traffice is minimized , etc.

I know tunning index is good, as far as my knowledge is about.

but my question still, if I create SP for all the MySQL query, anyway to quickly find out what SP this query is coming from ? if there is no way than we might still relies on the file search in the PHP tier. Then we change nothing and allow the application to hard to tune.
The complexity is increased if every query must be an SP on the database.  SPs reduce flexibility in adjusting modifying criteria.

SP have their place, you have to determine whether a set of querys that is relied on by multiple PHP pages to display certain info.
This deals with whether you have a similar set of queries in multiple phps versus a single SP that is referenced by those same pages.
If you have a "Static" or unchanging data-set from a query, as has been said, SP may be efficient and best for you, as far as "Speed" goes, there are many factors that are in effect for different queries and what the relationships for Tables (join and other) are, also for high traffic MySQL server pipelines, there are data server settings and hardware configures that can make tremendous differences in data server speeds for high traffic areas.
For Me, , The way to hammer out  the speed thing is to do speed tests for some things that you do, change it to another steps or procedures, and test again. There are some who specialize in Data Server set up and config for speed, that may can help, but again it may have many factors to deal with. Sorry if this seems non-helpful, but maybe a general "one size fits all" answer here will not be so easy. But in general I do believe that the stored procedures are faster in MySQL. But bad or inefficient queries are still crap even in SP.
marrowyungSenior Technical architecture (Data)Author Commented:

" also for high traffic MySQL server pipelines, there are data server settings and hardware configures that can make tremendous differences in data server speeds for high traffic areas."

Sorry we are in CNY and in holiday, please provide no guide on customizing the MySQL server size.

someone told that the we can move data from one MySQL data volume to another to offload the data load speed from one partitition, is that right? someone told me before that it can't do it !

for exampl,e if later on we buy a faster hard disk, we can move part of the LARGE data to that volume only, is that right? how to do it ?
/var/lib/mysql/highperformancedatabase can be a partition on this faster drive.

Though speed up benefits from larger availability of memory. In a comparison access to data stored in memory  is significantly faster than access on the hard drive no matter how fast.
you ask some about - " if later on we buy a faster hard disk, we can move part of the LARGE data to that volume",  I would say that it is possible, but you seem to think there are "One Size Fits All" ways (how to do it) for these sorts of things, and I do not think that is so, you would likely need to know the EXACT details of database and hard drive setup, and work out a solution for that system. This is not what I do (database optimization), but I have had to get someone with experience to do that for us, OR tell us what to do. Sorry for not having example, but that's just what I think.
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.