20 Different Tables querys slowing page display

[cesarcesar]

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)

 

[shoz]

20 queries seem like a lot for one page but we'll start by looking at query 1 and restructure the code in cases where it doesn't require a great deal of time.

 

Post the CREATE statement for each table in the first query, the EXPLAIN for the query and the time taken for its execution using the following

 

SHOW CREATE TABLE tablename
EXPLAIN SELECT ....... FROM ....

 

In PHP5 you can get the time to execute using

<?php
$time_start = microtime(true);
$vsv = $db_query();
$time_end = microtime(true);
$time = $time_end - $time_start;
echo $time;
exit();

 

microtime's manual page shows how to do the same using PHP4

 

EDIT: Also post the query itself after all variables have been expanded and the version of MYSQL you're using.

SELECT VERSION();