Optimizing a PHP page with many queries

Question

So i have a site that I am working on that has been touched by many developers over time and as new features arised the developers at the time felt it necessary to just add another query to get the data that is needed. Which leaves me with a php page that is slow and runs maybe 70 queries. Some of the queries are in the actual PHP file for this page and some are scattered throughout many different function. Now i have the responsibility of trying to speed up the page to meet certain requirements. I am seeking the best course of action.

1. Is there a way to print all the queries that are running other then going through the file and finding each and every one?
2. should I cache the queries that are slow using memcached?
3. Is there an idea that anyone has had to help me speed up the page?
4. Is there a plugin or tool to analyze the queries, I am using YSlow and there is nothing there to look at queries?

Answer 1

Something I do is to have a my_mysql_query(...) function that behaves as mysql_query(...) but which I can then tailor to log out the execution time together with the text of the query. MySQL can log slow queries with very little fiddling - see here.

Answer 2

1. If there is not a central query method that is called to run each query, then the only options is to look for each query and find where it is in the code. Otherwise you could go to that query function and print each query that runs through it.

2. Using cache will depend on how often the data changes. If it changes frequently, it may not give you any performance boost to cache it.

3. One idea to help you speed up the page is to do the following:

   • group like queries into the same query and use the data in multiple parts
   • consider breaking the page into multiple locations

4. Depending on the database you are using. There are analyze functions in some databases that will help you optimize your queries. For example, you can use EXPLAIN with mysql. (http://dev.mysql.com/doc/refman/5.0/en/explain.html) You may need to consider consulting with a DBA on the issue.

Good luck.