20 Different Tables querys slowing page display

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)

Code (markup):

 

caching won't really help your cause very much, if it's sheer number of queries slowing the page down, all you can really do is swap out some of the content for ajax, and then the rest of the page can load and display loading images, then ajax can take over each cell one by one.....

 

thanks for the reply. i have thought about Ajax, which is already integrated many times into the site, though i don't see.. or have learned yet how to use it in a query request and display rows scenario.

 

you would just use your code as the url you send the request to, same as normal ajax interaction, just put your queries in the right place and echo whatever you need to display

 

MySQL is a very very fast database server, I would look into my tables and indexes before changing a working code, or installing something new!

 

Its most likely a matter of design. Check your indexes etc... Perhaps its even the fundamental design of the data structure. Also make sure you performa regular maintenance on the database optimising and also recalcing table stats etc.

 

Hi, seems to me that it is probably bad design. The fact that 20 tables must be queried to supply information for a page seems over the top.

If after checking the indexes on the tables you still have a problem, look to see if it is necessary to query all these tables, is there some data you can do with out?? e.g. Is it really necessary to query the countries table when displaying info about a user etc?

Also, what about using Stored Procedures instead of dynamic SQL? Stored Procedures *are faster* then direct SQL calls.

Also, do not use the syntax SELECT * FROM

Explicitly name the records you need and no more e.g.

SELECT FirstName, LastName FROM Users

Finally, you could always look at implementing some sort batch update and temp tables. Find any queries which do not need "up to the second" data and which make up the bulk of the time that your queries run and store this information into a temp table at certain times during the day. In effect you are creating a database version of a cache, when you need this info, simply query this table and the info will be there already without having to do multiple joins across multiple tables.

Good luck
Regards
Markus

 

Find the worst query and optimize it. Then the second worst, etc. MySQL server should be able to run 20 well-optimized queries on modern hardware in a matter of a few milliseconds. Also compare the combined execution time of queries with the load time to account for how much of a bottleneck your PHP code is giving you.