Hello Fellow Programmers,

I have an issue with a very large PHP page that calls over 20 different MySql Statements. This page is loading somewhat slow and i want to speed things up. I have looked into sql caching applications like http://eaccelerator.net/ and http://www.danga.com/memcached/ which i may install later, but i feel they are more for pages that are called frequently, my page calls the same data less times but can call more than 2000 different versions of the data.

Being that my page is a little over 1000 lines, putting it all here is not gonna happen. I will try to shorten the code using examples... After writing the blow code.. it may be really confusing. I have stuck the file on my server http://www.empiresolutions.net/clien.../green_bar.zip in case anybody whats to get a better look. This file in it current state works fine, just is slow. Any and all help is so appreciated.

Another Question... Can anyone provide suggestions on how to bench-test my sql scripts to see exact improvment speeds?

Each indent means the following query is nested.

Query 1 (2 Join Tables)

Query 2 (2 Join Tables)

Query 3 (1 Table)

Query 4 (4 Join Tables, While Loop)

    Query 5 (2 Join Tables)

    Query 6 (1 Table)

Query 7 (4 Join Tables, While Loop)

    Query 8 (2 Join Tables)

    Query 9 (1 Table)

Query 10 (1 Table, While Loop)

    Query 11 (2 Join Tables, Calls every 10 rows from Query 10)

    Query 12 (2 Join Tables, Calls every 10 rows from Query 10, same as Query 11 basically)

    Query 13 (2 Table, While Loop)

        Query 14 (1 Table)

        Query 15 (2 Join Tables, This query and subs are called 4 times in a FOR loop)

            Query 16 (2 Join Tables)

            Query 17 (2 Join Tables)

                Query 18 (1 Table)

            Query 19 (2 Join Tables)

                Query 20 (1 Table)

Faster machine or less queries.

It may look like a lot of queries, but if they are optimized correctly and the db schema is consistent, it should not be too much of a problem.

I've used 2 method of profiling for the db queries in time.
The first, and most easy to implement is to use the microtime() fnuction just before sending the query and just after, and storing the delta in an associative array, like this:

PHP Code:

$sql=<<<SQL

SELECT *
FROM itemList
WHERE creaDt BETWEEN $startEpoch AND $endEpoch

SQL;
$beg=microtime(true);
$r=$objDb->doQuery($sql);
$end=microtime(true);
$_SESSION['sqlProfile'][$sql][]=$end-$beg; 


the $objDb object is a sql wrapper I designed specifically for my needs. I usually would place teh profile in the wrapper.
Anyway, after displaying the page, I would just point my browser on a page who dumps the session profiling array, and it will display me how many times each query have been ran, and how many microseconds it have taken.
Remember that this time may include the db connection time and data transfer, not only the db query time.
If your query return a lot of rows, it may be a concern.


The second way of profiling would be with the excellent xdebug (http://www.xdebug.org) php extension, and with the wincachgrind utility (http://sourceforge.net/projects/wincachegrind/)

When configured to do so, xdebug can output a trace file on your server (documentation and screenshots here). By fetching this file and giving it to wincachegrind, you can have a listing of each and every function, native or custom, that the php engine has called, how many times they where called, and how many time on the total run time they took.
It may be difficult to read, especially if you are not confident with profiling tools, but when you seriously want to optimize, this is the way to go.

Try using some sort of cache system? At the place I work we have a slow proprietary database system used to manage our products - spooling data takes forever; so we cache all searches preformed for about 30 minutes. Obviously it depends on what kind of data your receiving, if it's always unique, then caching wont work.

MySQL has its own query cache, you can turn it on in the mysql.config file

Indeed it has, but it may be a bit too low on requirements.
As far as I know, it caches data until an INSERT or an UPDATE is done on the table.
So, if you update regulary 1 row of the table, the caching won't be done. I would not rely too much on that mechanism.

If that's true, you should switch to SQL Server.

Well, I stand on my ground:
From the mysql help reference: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.
The query cache is extremely useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.
Note: The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

So again, if your datas don't change much, perfect, but in my opinion, Mysql is a toy.
A useful toy, but a toy nonetheless.
I've heard that you could use different back-end for your tables. And each back-end can have a different DB engine, but I never tried them.

If you want a real industry strenght DB, look for postgresql in the open source world, and oracle in the commercial world.