Page 1 of 1

Extremely slow loading PHP page help.

Posted: Sun Oct 23, 2011 5:48 pm
by rich95
Hey gang;

Among the several websites I have, one is called CGSphere.com (http://www.cgsphere.com) and it's been going for some time. It's a gallery website of sorts and a fun, free creative competition. It's always had a very slow load page time on the pages that require loading and displaying various thumbnails, to the point where it's excruciatingly slow and often takes up to 10 seconds to load, or longer. More and more users are complaining about it and I've promised to see if I can improve it.

After some testing, I've come to realize it's most likely the SQL query (or something to do with it) since it's only slow on the front page and the gallery page, the two that display thumbnails. The other ones, are very quick and I believe have limited, or no access to the DB. A couple of friends have suggested the sql query might be the source of the problem.

I thought perhaps its the speed of my server but I have other websites as well on my dedicated root server that also access their own database and their speed is just fine so this proves it's nothing to do with a slow server.

I'm not much of a PHP developer but can manage my way around minor changes and modifications. An associate had previously built the site for me but he's unavailable at this time. I was hoping someone could take a look at it and perhaps shed some light on where they think the problem may lie and, if it's easy enough, I might be able to make the modifications myself (or even better, with their help).

The very slow pages in specific are http://www.cgsphere.com and http://www.cgsphere.com/gallery/

While clicking on any of the other ones results in nice, speedy refreshes.

I really appreciate any help on this. I'm at a loss at this point and require the assistance of professionals. ;)

Thanks,
-Richard

Re: Extremely slow loading PHP page help.

Posted: Sun Oct 23, 2011 6:21 pm
by genix2011
Hi,

yeah can't do much without seeing some code. You could perhaps try to activate the mysql slow query log, and set it to lets say 2 seconds, with this method you can find mysql performance issues.

You can activate this in the my.cnf by adding these lines:
[syntax]
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
[/syntax]

Details: http://dev.mysql.com/doc/refman/5.1/en/ ... y-log.html

Greets.

Re: Extremely slow loading PHP page help.

Posted: Sun Oct 23, 2011 10:06 pm
by rich95
Thanks for this info. Would it be helpful if I cut and paste the code for the two pages on this thread? I assumed it could be seen through a web browser?

Please let me know what would help.

Thanks,
-Richard

Re: Extremely slow loading PHP page help.

Posted: Sun Oct 23, 2011 11:08 pm
by twinedev
If you are pretty sure it is the database, then things to look for are:

1. Size of the tables, since it didn't used to be as bad, perhaps now it is that you are just getting more data

2. Tables having proper indexes. While I'm not the best at these, if you have a lot of data, and you are searching based upon something not indexed, can take longer (wish i had more real world experience to learn them better)

3. Joining of data between tables, i once set up a product display page, that had a several tables, that just doing a non optimized raw join, kid you not, took 20 seconds to display the page. Ended up taking the larger tables that had the main where part, and selecting that into a temp table, then doing the join on the temp table.

Besides the above, depending on what data you are retreiving, you may want to look into caching the results from the slow queries, ie, take all their results, write it out to a text file using the serialize() function. Just be sure any activity on the site that may alter those results should include a statement to clear out the temp file.

@genix2011 Thanks for the info on that, had known you could do that, but never knew where it was.

-Greg

Re: Extremely slow loading PHP page help.

Posted: Mon Oct 24, 2011 10:53 am
by egg82
also remember: It's quicker to sort the data in the query, rather than the PHP.

An example:

Code: Select all

$result = mysql_query("SELECT * FROM `table`");
while($row = mysql_fetch_array($result)){
	if($row["id"] == 1){
		//do something
	}
}
//BAD CODE

Code: Select all

$result = mysql_query("SELECT * FROM `table` WHERE `id`=1");
while($row = mysql_fetch_array($result)){
	//do something
}
//GOOD CODE

Re: Extremely slow loading PHP page help.

Posted: Tue Oct 25, 2011 12:22 pm
by genix2011
Hi,

sorry @egg82 that is no good code, the while-loop is not neccassary, also there is nothing sorted, just filtered.

Code: Select all

$result = mysql_query("SELECT * FROM `table` WHERE `id`=1");
$row = mysql_fetch_array($result);
Also it would be better to use mysqli, because mysql will be removed in future PHP versions.

Greets.

Re: Extremely slow loading PHP page help.

Posted: Tue Oct 25, 2011 12:28 pm
by egg82
`id` doesn't imply that it's a primary key

Basically i'm just preparing for all occasions (including multiple rows)