[SOLVED] Creating a cache of categories children

[Renlok]

I have a script with categories, it has thousands of categories and theres a page where it needs to build a list of all the children categories and it takes forever to list this. so i want to make a cache of each categories children.

 

I cant figure out how to traverse all the categories to make caches for them. anyone have any ideas of how to do this or know of any scripts that can do this?

[rhodesa]

The answer to this question is going to be HIGHLY dependent on how you have things setup. Can you elaborate on what your current table structure is and how Parents/Children are linked?

[Renlok]

categories at the moment are linked by parent_ids so the stucture is like

 

cat_id | parent_id | category_name

 

[soak]

If your script that generates the categories isn't 1000's of lines long can you post the relevant bits of it please?

 

It is better to optimise that first and if further optimisation / caching is required we can advise.

 

Also, how many lines of data in the relevant tables?

[Renlok]

Well on the page where it gets the children the code is

 

function getsubtree($catsubtree, $i)
{
global $catlist, $DBPrefix, $system;
$query = "SELECT cat_id FROM " . $DBPrefix . "categories WHERE parent_id = " . $catsubtree[$i];
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);
while ($row = mysql_fetch_assoc($res))
{
	$catlist[] = $row['cat_id'];
	$catsubtree[$i + 1] = $row['cat_id'];
	getsubtree($catsubtree, $i + 1);
}
}

$catsubtree[0] = $id;
$catlist = array();
$catlist[] = $catsubtree[0];
getsubtree($catsubtree, 0);
$catalist = join(',', $catlist);

 

and that gets the children for the one category your currently looking at. And there's about 12,000 lines in the table. Running on my localhost it takes about 50 seconds to make the list.

[soak]

An index on parent_id will help if there's not one there already. I'm still thinking about how to optimise

[laffin]

Categories are rarely updated.

This is wut Ive done.

I create a cache file with the array serialized

on the category editor, is when I build this cache file.

 

Yeah, it may take 50 secs to generate the list

but it shud take under a sec to read a file/unserialized the generated list.

 

 

 

 

[rhodesa]

Categories are rarely updated.

This is wut Ive done.

I create a cache file with the array serialized

on the category editor, is when I build this cache file.

 

Yeah, it may take 50 secs to generate the list

but it shud take under a sec to read a file/unserialized the generated list.

 

this is a good way of doing it. another way is to cache the subtree right in the mysql table. so, create a TEXT field in your categories table to store all the subtree info, and create a function that gets run whenever a category is changed. put a serialized version of the array in that field

[soak]

There's a great article here on better ways to store your data so that you can get the info you need with one query.

 

EDIT: And here.

[Renlok]

hey soak thanks for the links, it works much faster using nested storage

[laffin]

this is a good way of doing it. another way is to cache the subtree right in the mysql table. so, create a TEXT field in your categories table to store all the subtree info, and create a function that gets run whenever a category is changed. put a serialized version of the array in that field

 

Ya know I never thought of using the MYSQL for cache storage, this is an excellant idea.

The problem of using files as storage, is the permissions, so ya either have to create an empty file and chmod it 0777 or create an empty folder chmod. Both systems have their pros and cons.

 

But using a db, yer not worried about the permissions, ya can store it into a simple table like:

CREATE TABLE cache {
   id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
   name VARCHAR(30) NOT NULL,
   cache TEXT
}

 

or similar, I really do like this idea rhodesa, cant believe didnt think about doing it before.