time out issues

[Renlok]

I have the function

function search_cats($parent_id, $level) {
global $DBPrefix;
$query = "SELECT cat_id, cat_name FROM ".$DBPrefix."categories
		WHERE deleted = 0 AND parent_id = $parent_id ORDER BY cat_name";
$result = mysql_query($query);
$cats = array();
$catstr = '';
$stringstart = '';
if($level > 0) {
	for($i = 0; $i < $level; $i++) {
		$stringstart .= '|___';
	}		
}
while($cats = mysql_fetch_array($result)) {
	$catstr .= ",\n{$cats['cat_id']} => '$stringstart{$cats['cat_name']}'";
	$catstr .= search_cats($cats['cat_id'], $level+1);
}
unset($cats);
return $catstr;
}

 

which sorts categories into a tree but theres a problem when there are say 1000+ categories it will time out as it runs the query for each category to check for child categories does anyone have an idea of how i could speed this up?

[premiso]

Thats alot of categories to have. As far as I know there is no way to really make that more efficient/speed it up...you could switch from mysql_fetch_array to mysql_fetch_assoc since array fetches double the data where as assoc only does 1 set.

 

Other than that change the timeout of your script, or don't do it dynamically and have a cache, when a new category is added, update the cache on the spot then just refer to the cached version for everything else...

[Renlok]

well ive change mysql_fetch array to mysql_fetch_assoc makes a bit of difference but not much and that is the script that builds the category cache.

 

Just wondering but would it be faster you get all the categories and to find the children categories just search the array repeadedly