Can this code be improved to work faster?

damnedbee

So far I've been learning PHP by example by customizing a prewritten script.

I have two pages. Both return about the same number of results using the same tables. Page One returns the last 15 stories posted; Page Two returns all stories posted in a single category.

However, Page One loads very slowly and sometimes times out, while Page Two is fine. I’m guessing it has to do with how Page Two handles arrays, but I’m not sure.

I would really appreciate your help with two things:

Help me understand conceptually why Page One loads so much slower.
Help me fix Page One to function more like Page Two. It would be very helpful to see some code examples to learn from.

Here are snippets of code from both pages:

Page One (the slow one)

<?

$table = $dl->select(
	"s.*, u.*, a.*,cg.*,sg.*, COUNT(DISTINCT c.cid) AS chap, COUNT(DISTINCT r.rid) AS rev",
	"sl18_stories s 
		LEFT JOIN sl18_chapters c ON c.csid=s.sid 
		LEFT JOIN sl18_users u ON u.uid=s.suid 
		LEFT JOIN sl18_rate a ON a.ratsid=s.sid 
		LEFT JOIN sl18_category cg ON cg.caid=s.scid 
		LEFT JOIN sl18_subcategory sg ON sg.subid=s.ssubid 
		LEFT JOIN sl18_review r ON r.rsid=s.sid",
	"",
	"GROUP BY s.sid ORDER BY s.stime DESC LIMIT 0,15"
	);

if ( count( $table ) < 1 ) 
	print "There are currently no published stories.";
else {

	foreach($table as $row) {

?>

Page Two


<?

$table = $dl->select("*","sl18_stories",array('ssubid'=>$_GET["list"]));

if ( count ( $table ) == 0 ) {

print "There are no stories contained in this sub-category";
print "</td></tr>";

} else {

$total = count( $table );
$total = ceil($total / 15);

if ( !isset ( $_GET["page"] ) || $_GET["page"] == 1 ) {				
	$page = 1;
	$getpage = 0;

} else {
	$page = $_GET["page"];
	$getpage = $_GET["page"] * 15 - 15;
}

$table = $dl->select(
	"s.*, u.*, a.*, COUNT(DISTINCT c.cid) AS chap, COUNT(DISTINCT r.rid) AS rev",
	"sl18_stories s 
		LEFT JOIN sl18_chapters c ON c.csid=s.sid 
		LEFT JOIN sl18_users u ON u.uid=s.suid 
		LEFT JOIN sl18_rate a ON a.ratsid=s.sid 
		LEFT JOIN sl18_review r ON r.rsid=s.sid",
	array('s.ssubid'=>$_GET["list"]),
	"GROUP BY s.sid ORDER BY s.sname ASC LIMIT $getpage,15"
) or die(
	$dl->getError()
);

?>

volleytotal_ch

guess that the script is so long because of the joins.
- can you post your function "select" of the class?
- can you post the table structures and how they're linked (just the needed cols!)

demonicfoetus

I haven't really got time to analyse completely (going out) but wot i first notice about page 2 is that it uses $_GET etc. These short versions run slower on the server, using the strict method of HTTP_GET_VARS etc will run faster. As page 1 doesn't have any of these shortenings it will fun faster.

volleytotal_ch

i'll try - may not work 😉 (Query for page one)

SELECT
   whatever_you_need_but_not_more
FROM
  sl18_stories s,
  sl18_users u,
  sl18_rate a,
  sl18_category cg,
  sl18_subcategory sg
LEFT JOIN
  sl18_chapters c ON c.csid=s.sid
LEFT JOIN
  sl18_review r ON r.rsid=s.sid
WHERE
  u.uid=s.suid 
  AND a.ratsid=s.sid 
  AND cg.caid=s.scid
  AND sg.subid=s.ssubid
GROUP BY
  s.sid
ORDER BY
  s.stime DESC
LIMIT 0,15

hth

damnedbee

I can already tell that I'm too much of an ignorant newbie to be helped by your answers. Thanks for the code, Volley, but at this stage, I'm still not even sure about proper syntax. All I can really do right now is change existing code until I get the effect I want. 🙁

With that in mind, I'm going to come at this another way.

Here is the code that works. My comments follow below.


<?

$table = $dl->select("*","sl18_stories",array('ssubid'=>$_GET["list"]));

if ( count ( $table ) == 0 ) {

print "There are no stories contained in this sub-category";
print "</td></tr>";

} else {

$total = count( $table );
$total = ceil($total / 15);

if ( !isset ( $_GET["page"] ) || $_GET["page"] == 1 ) {				
	$page = 1;
	$getpage = 0;

} else {
	$page = $_GET["page"];
	$getpage = $_GET["page"] * 15 - 15;
}


$table = $dl->select(
	"s.*, u.*, a.*, COUNT(DISTINCT c.cid) AS chap, COUNT(DISTINCT r.rid) AS rev",
	"sl18_stories s 
		LEFT JOIN sl18_chapters c ON c.csid=s.sid 
		LEFT JOIN sl18_users u ON u.uid=s.suid 
		LEFT JOIN sl18_rate a ON a.ratsid=s.sid 
		LEFT JOIN sl18_review r ON r.rsid=s.sid",
	array('s.ssubid'=>$_GET["list"]),
	"GROUP BY s.sid ORDER BY s.sname ASC LIMIT $getpage,15"
) or die(
	$dl->getError()
);

?>

It's currently using this to list the results:

table: sl18_subcategories
field: subid

But I want to use this:

table: sl18_stories
field: sid

Can you show me what I need to replace?

I hope I'm making sense. Newbies suck.

volleytotal_ch

can you post the function "select" in your class?