what can I do to optimize the following function o

问题:

I am working on business directory sort of thing, and need to show recursive parents of categories at category listing.

I am using following function for that:

    public function get_recursive_parents($category_id){
        $categories = array();
        $res = $this->db->from('categories')->where('cat_id',$category_id)->get()->row_array();
        $cat_id = $res['parent_id'];
        $categories[] = $res;
        while($cat_id){
            $res = $this->db->from('categories')->where('cat_id',$cat_id)->get()->row_array();
            $categories[] = $res;
            $cat_id = $res['parent_id'];
        }
        return $categories;
    }

I am using this function and as it is on admin site and a bit slow at admin site can is fine too, and admin will be only one so I can give it more memory.But I think limit memory more than 300M for one call is too much and still getting this:

Fatal error: Allowed memory size of 367001600 bytes exhausted (tried to allocate 72 bytes) in /var/www/usmanproject/salesfinder/system/database/DB_active_rec.php on line 2007 

So is there way so that I can optimize above function? or I need to do some specific sort of indexing or algo optimization, or any other possible way? Or I just stop showing all parents and super parents of category (that is client demand to see hierarchy)? Or need to increase memory as I already worked on a directory and that was also slow at admin site so I guess they were just using more Memory?

Any advice will be appreciated.

---

Here is that table schema, it has parent_id so it is working as recursive relation.

   CREATE TABLE IF NOT EXISTS `categories` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(255) DEFAULT NULL,
  `cat_title` varchar(255) DEFAULT NULL,
  `cat_desc` varchar(255) DEFAULT NULL,
  `cat_text` text,
  `parent_id` int(11) NOT NULL,
  `cat_img` varchar(255) DEFAULT NULL,
  `sort_id` int(11) NOT NULL DEFAULT '1',
  `last_level` tinyint(4) NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=221 ;

回答1:

try to use below code

public function get_recursive_parents($category_id,$categories=array())
{
    if($category_id!="")
    {
        $new_ar1=array();
        $fe = $this->db->from('categories')->where('cat_id',$category_id)->get()->row_array();
        array_push($new_ar1,$fe["parent_id"]);
        return $new_ar1;
    }
    else
    {
        $res = $this->db->from('categories')->get()->row_array();
        array_push($categories,$res['parent_id']);
        $categories[$res['parent_id']]=array();

        array_push($categories[$res['cat_id']],get_recursive_parents($res['parent_id'],$categories));
    }

    return $new_ar;
}

call the function

get_recursive_parents($category_id);

hope it will help you

回答2:

Issue is solved, there was actually a record whose parent_id was pointing to its own cat_id that is primary key. So that was pointing to itself and in that case that recursion simply wasn't ending. I used while loop and that become infinite in this case.

However during debugging I found this post that was helpful, http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ It provide better ways to handle same thing. In my scenario self join is useful as mentioned in this post.