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)

 

[jpadie]

there is a school of thought that suggests that adding more RAM and/or processors is cheaper and quicker than trying to do serious query optimisation.

but what can we say: you have not given us any table structure or information on the recordsets you are looking to retrieve so i am not sure how we could help! further i would guess that what you're really looking for is help in sql optimisation, in which case the mysql forum is more likely to be the proper port of call.

 

[cesarcesar]

http://www.empiresolutions.net/clients/zell/structure.zip

this is the database structure. 77 tables. ouch i know.

 

[bam720]

I don't know if it will help but I found this over in the html forum....

http://www.websiteoptimization.com/services/analyze/

Maybe you're missing something in your page that could be reduced. Im sure you were looking more for optimization solutions for your tables and queries, but without a physical example its hard to tell.