Extremely slow loading PHP page help.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rich95
Forum Newbie
Posts: 3
Joined: Mon Oct 27, 2008 5:59 pm

Extremely slow loading PHP page help.

Post 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
genix2011
Forum Commoner
Posts: 74
Joined: Tue Aug 02, 2011 4:00 pm

Re: Extremely slow loading PHP page help.

Post 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.
rich95
Forum Newbie
Posts: 3
Joined: Mon Oct 27, 2008 5:59 pm

Re: Extremely slow loading PHP page help.

Post 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
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Extremely slow loading PHP page help.

Post 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
User avatar
egg82
Forum Contributor
Posts: 156
Joined: Sat Oct 01, 2011 9:29 pm
Location: Colorado, USA

Re: Extremely slow loading PHP page help.

Post 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
genix2011
Forum Commoner
Posts: 74
Joined: Tue Aug 02, 2011 4:00 pm

Re: Extremely slow loading PHP page help.

Post 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.
User avatar
egg82
Forum Contributor
Posts: 156
Joined: Sat Oct 01, 2011 9:29 pm
Location: Colorado, USA

Re: Extremely slow loading PHP page help.

Post by egg82 »

`id` doesn't imply that it's a primary key

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