Page 1 of 2

Slowing down a script?

Posted: Mon Mar 01, 2004 6:42 pm
by Mr. Tech
Lets say I have a links directory script that creates folders for the categories and saves the links in a MySQL Database and has 25k folders and 30k links.

What would be a reason for this to slow down?

Thanks

Posted: Mon Mar 01, 2004 7:38 pm
by pickle
Slow down from what? What is it that you think is slowing down your script?

Posted: Mon Mar 01, 2004 8:00 pm
by Mr. Tech
Having so many links and categories might slow it down... I just want to ask if the script can slow down somehow... Is this possible?

Posted: Mon Mar 01, 2004 8:19 pm
by qads
at which point do you think it slows down? while showing categories or links?...or both? howmany queries do you have in each section? (to show categories and links)

have you tested other scripts on ur server? install phpbb or phpnuke or something else to test it.

Posted: Mon Mar 01, 2004 9:18 pm
by redhair
Mr. Tech, a reason why it may be slow is:

a: your server is just slow
b: Your script is..


Provide the url/ script so it can be looked at.

Posted: Mon Mar 01, 2004 9:22 pm
by Mr. Tech
So the script wouldnt really be any different if it had 5 links or 30k?

Posted: Mon Mar 01, 2004 9:23 pm
by Mr. Tech

Posted: Mon Mar 01, 2004 9:28 pm
by redhair
That url is fast enough...whats the problem>?

Posted: Mon Mar 01, 2004 9:30 pm
by tim
hmmmm, the page loaded for me in seconds...

and I am on a puny dial-up... No problems on my end?

Posted: Mon Mar 01, 2004 9:31 pm
by Mr. Tech
Maybe I havn't said this clear enough. Sorry.

I'm not experiancing problems now I'm just asking if this can happen in the future?

Posted: Mon Mar 01, 2004 9:37 pm
by redhair
Mr. Tech wrote:Maybe I havn't said this clear enough. Sorry.

I'm not experiancing problems now I'm just asking if this can happen in the future?
As long as you don't provide the code there is no telling it will.

For now it seems to work, and if you don't want to reveal your code, wait untill it actualy does deliver speed problems...

Posted: Mon Mar 01, 2004 10:49 pm
by qads
just make sure you dont show like 2000 links perpage, split them into pages, like 100 perpage maybe.

Posted: Mon Mar 01, 2004 11:14 pm
by m3mn0n
Mr. Tech wrote:Maybe I havn't said this clear enough. Sorry.

I'm not experiancing problems now I'm just asking if this can happen in the future?
In theory, yes. The length of time the PHP/MySQL engines takes to parse data grows with the ammount of data there. But these two applications are optimized for performance and are constantly being made to work better, and more efficent.

In all honesty I wouldn't worry about it unless you notice obvious, and longer than normal script executions and/or people start to complain.

I have a suggestion for you; add a script execution timer to your site and log the number of total links/catagories along side the day's average script execution time. This will give you a good comparison, and as it grows, you can see exactally what difference the new size makes from previous ones.

Posted: Mon Mar 01, 2004 11:49 pm
by Mr. Tech
So basically what you are saying is it may take longer to select the information from the database when there is a lot of data?

Posted: Tue Mar 02, 2004 12:00 am
by Mr. Tech
Here's a bit of code from the page that displays the links and categories:

Code: Select all

<?php
//
// Redirect
//
$redirect = mysql_fetch_array(mysql_query("select redirect from ".$tbl_name."_cats where cat='$overallid'"));
$redirect = $redirect[redirect];
if ($redirect == "-1") {
// No redirect
} else if ($redirect == "0") {
$redirect_path = $config[sitesurl];
header ("location: $redirect_path");
exit;
} else {
$redirect_to = mysql_fetch_array(mysql_query("select pathtxt from ".$tbl_name."_cats where cat='$redirect'"));
$redirect_to = $redirect_to[pathtxt];
$redirect_path = str_replace("|O|", "/", $redirect_to);
$redirect_path = str_replace(" ", "_", $redirect_path);
$redirect_path = $config[sitesurl].$redirect_path;
header ("location: $redirect_path");
exit;
}

$locationa = show_location($id, $config[replace]);
$pagetitle = "$lang[top]"."$locationa";

$metadesc = mysql_fetch_array(mysql_query("select metadesc from ".$tbl_name."_cats where cat='$id'"));
$metakeys = mysql_fetch_array(mysql_query("select metakeys from ".$tbl_name."_cats where cat='$id'"));
$null = "<META name="description" content="{DESC}">\n<META name="keywords" content="{KEYS}">";
$null = str_replace("{DESC}", $metadesc[metadesc], $null);
$null = str_replace("{KEYS}", $metakeys[metakeys], $null);

include($config[rootpath]."includes/header.php");

print "$header";

// ---------------------------------------------------
// Show Categories
// ---------------------------------------------------

// Defualt Sort
if (!$sort) $sort = "uptime";
if (!$order) $order = "desc";

// Location
$show_location = "select * from ".$tbl_name."_cats where cat='$id'";
$show_location_result = mysql_query($show_location) or die("<b>MySQL Error:</b> " . mysql_error());
$numrows = mysql_num_rows($show_location_result);
for($x=0;$x<$numrows;$x++) {
	$resrow = mysql_fetch_row($show_location_result);
	$cat_id = $resrow[0];
	$parent = $resrow[1];
	$path = $resrow[2];
	$pathtxt = $resrow[3];
	$title = $resrow[4];
	$description = $resrow[5];	
	$pathtxt = title_fix($pathtxt);
	$patha = explode("/",$path);
	$pathtxta = explode("|O|",$pathtxt);

	for ($i=0 ; $i <count($pathtxta) ; $i++) 
	{
	$titlea = mysql_fetch_array(mysql_query("select pathtxt from ".$tbl_name."_cats where cat='$patha[$i]'"));
	$title = str_replace("|O|", "/", $titlea[pathtxt]);
	$title = str_replace(" ", "_", $title);
	if ($id == "$patha[$i]") {
	$mainlocation .= "$config[replace]"."$pathtxta[$i]";
	} else {
	$mainlocation .= "$config[replace]<a href="$config[sitesurl]"."$title">$pathtxta[$i]</a>";
	}
	}
}

// Show Categories
$show_cats = "select * from ".$tbl_name."_cats where parent='$id' order by title asc";
$show_cats_result = mysql_query($show_cats) or die("<b>MySQL Error:</b> " . mysql_error());
$numrows = mysql_num_rows($show_cats_result);
$half = intval($numrows / $config[catrows]);
if (($half+$half)!=$numrows) $half = $half + 1;
if ($numrows==0) $showsubcats .= "<tr><td class='light'><blockquote><br>$lang[noscats].<br></blockquote></td></tr>";
for($x=0;$x<$numrows;$x++){
	$resrow = mysql_fetch_row($show_cats_result);
	$cat = $resrow[0];
	$parent = $resrow[1];
	$path = $resrow[2];
	$pathtxt = $resrow[3];
	$title = $resrow[4];
	$description = $resrow[5];
	$redirect = $resrow[8];

	// New Links / Total Links
	$newcata[$cat] = new_links($cat, $path, $config[newlinks], $config[rootpath], $config[template], $config[sitesurl]);
	$links = total_links($cat, $path);
	
	if ($redirect == "-1") {
	$catpath = str_replace("|O|", "/", $title);
	$catpath = str_replace(" ", "_", $catpath);
	} else if ($redirect == "0") {
	$catpath = $config[sitesurl];
	$title .= "@";
	} else {
	$redirect_t = mysql_fetch_array(mysql_query("select pathtxt from ".$tbl_name."_cats where cat='$redirect'"));
	$redirect_t = $redirect_t[pathtxt];
	$catpath = str_replace("|O|", "/", $redirect_t);
	$catpath = str_replace(" ", "_", $catpath);
	$catpath = $config[sitesurl].$catpath;
	$title .= "@";
	}
	$title = title_fix($title);

	$catlinks = fileread($config[rootpath]."templates/$config[template]/catlinks.php");
	$catlinks = str_replace("{CATPATH}", $catpath, $catlinks);
	$catlinks = str_replace("{CAT}", $cat, $catlinks);
	$catlinks = str_replace("{TITLE}", $title, $catlinks);
	$catlinks = str_replace("{LINKS}", $links, $catlinks);
	$catlinks = str_replace("{DESC}", $description, $catlinks);
	$catlinks = str_replace("{NEW}", $newcata[$cat], $catlinks);
	$catlinks = str_replace("{SUBCAT}", "", $catlinks);

	// Show the categories
	if ($x==$half) $showsubcats .= "$rowstoshow_index";	
	$showsubcats .= "$catlinks";
	}

// ---------------------------------------------------
// Generate Pages
// ---------------------------------------------------

// Get total links
$gettotall = mysql_fetch_array(mysql_query("select count(*) as links from ".$tbl_name."_links where cat='$id'"));
$Spagecut = "$config[linksperpage]";
$page_num = ceil($gettotall[links] / $Spagecut);
$page = ($page) ? $page : 1;
$vstart = $Spagecut * ($page-1);
$Sdirectcut = "$config[linksperpage]";
$page_start = floor(($page-1)/ $Sdirectcut ) * $Sdirectcut ; 
$page_end = $page_start + $Sdirectcut; 
for ($p=$page_start+1 ; ($p <= $page_end) && ($p <= $page_num)  ; $p++ ) 
{
if ($page == $p)	$direct_bar .= "[<b>$p</b>]";
	else 	$direct_bar .= "[<a href='index.php?page=$p&sort=$sort&order=$order'>$p</a>]";
}
if ($gettotall[links] > $vstart+$Spagecut ) {
$next_p=$page+1;
$next_list = "<a href='index.php?page=$next_p&sort=$sort&order=$order'>$lang[next]</a>&nbsp;\n";
}
if ($page>1) {
$prev_p=$page-1;
$prev_list="<a href='index.php?page=$prev_p&sort=$sort&order=$order'>$lang[prev]</a>\n";
}

$gettotalla = mysql_fetch_array(mysql_query("select count(*) as links from ".$tbl_name."_sponsors where cat='$id' or cat='all'"));
$Spagecut2 = "$config[linksperpage]";
$page2 = ($page2) ? $page2 : 1;
$vstart2 = $Spagecut2 * ($page2-1);

$sponsors = "select * from ".$tbl_name."_sponsors where cat='$id' or cat='all' order by id desc limit $vstart2,$Spagecut2";
$sponsors_result = mysql_query($sponsors) or die("<b>MySQL Error:</b> ". mysql_error());
$numrows = mysql_num_rows($sponsors_result);
$half = intval($numrows / $config[linkrows]);
if (($half+$half)!=$numrows) $half = $half + 1;
for($x=0;$x<$numrows;$x++){
	$resrow = mysql_fetch_assoc($sponsors_result);
	unset($location);

	if ($resrow[clicks] < 1 && $resrow[views] < 1) {

	} else {
	$sponsored_link = "yes";
	$spon = "yes";
	$showslinks .= show_links("id='$resrow[linkid]'",$lang[nosponsors],$vstart2,$Spagecut2,$sort,$order);
	}
}
if ($showslinks == "") {
$showslinks = "<tr><td class='light'><blockquote><br>$lang[nosponsors]<br></blockquote></td></tr>";
}
	unset($sponsored_link,$spon);
	$is_cat = "yes";
	$showlinks = show_links("cat='$id'",$lang[nolinks],$vstart,$Spagecut,$sort,$order);
	$dir = fileread($config[rootpath]."templates/$config[template]/dir.php");
	$dir = str_replace("{DIR}", $direct_bar, $dir);
	$dir = str_replace("{NEXT}", $next_list, $dir);
	$dir = str_replace("{PREV}", $prev_list, $dir);

	// Show the links and sub categories
	$catlinks = fileread($config[rootpath]."templates/$config[template]/cat.php");
	$catlinks = str_replace("{SITESURL}", $config[sitesurl], $catlinks);
	$catlinks = str_replace("{TOP}", $lang[top], $catlinks);
	$catlinks = str_replace("{LOCATION}", $mainlocation, $catlinks);
	$catlinks = str_replace("{ID}", $overallid, $catlinks);
	$catlinks = str_replace("{SEARCH}", $lang[search], $catlinks);
	$catlinks = str_replace("{SHOWSUBCATS}", $showsubcats, $catlinks);
	$catlinks = str_replace("{DIR}", $dir, $catlinks);
	$catlinks = str_replace("{LINKS}", $lang[links], $catlinks);
	$catlinks = str_replace("{TLINKS}", $gettotall[links], $catlinks);
	$catlinks = str_replace("{SPONSOREDLINKS}", $lang[sponsored], $catlinks);
	$catlinks = str_replace("{SPONSORED}", $showslinks, $catlinks);
	$catlinks = str_replace("{CATLINKS}", $showlinks, $catlinks);
	$catlinks = str_replace("{TEMPLATE}", $config[template], $catlinks);
	$catlinks = str_replace("{NEW_1}", $lang[new_1], $catlinks);
	$catlinks = str_replace("{NEW_3}", $lang[new_3], $catlinks);
	$catlinks = str_replace("{NEW_7}", $lang[new_7], $catlinks);

	$content = "$catlinks";

?>
Does that help?