20 Different Tables querys slowing page display

[empiresolutions]

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/clients/zell/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)
 

[ronverdonk]

Have you ever used the MySQL EXPLAIN function as a way to obtain information about how MySQL executes a SELECT statement?

That way you can get information on your queries. See Optimizing queries with EXPLAIN

Ronald :cool: