Massive performance degradation rendering menus with submenus

Whenever I render a menu item, for each of the menu item I found from the database, I use it to check its sub-menus.

My Controller rendering the menu item and recursive function to detect its sub-menus are as follows

public function renderAction()
{
    $menu = $this -> _request -> getParam('menu');
    $itemArray = $this -> getSubItems($menu);
    $container = new Zend_Navigation($itemArray);
    $this -> view -> navigation() -> setContainer($container);          
}

private function getSubItems($menu, $parent = 0) {
    $mapperMenuItem = new Apanel_Model_Mapper_MenuItem();
    $menuItems = $mapperMenuItem -> getItemsByMenu($menu, $parent);
    if(count($menuItems) > 0) {
        $itemArray = array();
        foreach($menuItems as $item) {
            $label = $item -> label;
            $uri = $this -> getSubItemUrl($item);               
            $subItems = $this -> getSubItems($menu, $item -> id);           
            if(count($subItems)) {              
                $tArray['pages'] = $subItems;
            }
            $tArray['label'] = $label;
            $tArray['uri'] = $uri;
            $itemArray[] = $tArray;
            unset($tArray);
        }
        if(count($itemArray)) {
            return $itemArray; 
        } else {
            return null;
        }       
    } else {
        return null;
    }       
}

   private function getSubItemUrl($item) {

        if(!empty($item -> link)) {
            $uri = $item -> link;                       
        } else {
            $pageMapper = new Apanel_Model_Mapper_Page();

            $details = $pageMapper -> getPageDetails($item -> page_id);             
            $pageClass = "CMS_Content_Item_".ucwords($details['nam开发者_高级运维espace']);
            $page = new $pageClass($item -> page_id);
            $title = str_replace(" ", "-", strtolower($details['name']));
            $uri = $this -> view -> url(array(
                "namespace" => $details['namespace'],
                "title"     => $title
            ),'page-view');
        }
        return $uri;            
    }

And function getItemsByMenu in MenuItem Mapper

public function getItemsByMenu($menuId, $parent = 0)    {
    $select = $this -> getDbTable() -> select();        
    $select -> where("menu_id = ?", $menuId)
            -> where("parent = ?", $parent)
            -> order("position");
    $items = $this -> getDbTable() -> fetchAll($select);
    if($items -> count() > 0) {
        return $items;
    } else {
        return null;
    }
}

I have about 4 different types of menu rendered in my app and I am noticing significant performance degradation in the execution. I often get Execution timeouts, the difference between the rendering time with the menus are about 35 sec and without are 22 sec approx. And this all in localhost. Is there any flaw in my recursion? What measure can I take to improve the performance of the code?

I can't see anything in there that would explain 35 second execution time, unless you have 100,000s of items in your menus table with no indexes at all. Suggestions:

1. Make sure you have an index on the menu items table on: menu_id, parent, position (that's one index on three fields, with the fields in that order.

2. I assume getPageDetails is doing another database query. Ideally you'd want to load these details when you load the menu items (by joining in the pages table), so you could then just pass the array of page data to getPageDetails instead of having to do an additional query per item.

If that doesn't give any miraculous improvements, try enabling the DB profiler so you can see whether it's volume or speed of the database queries that is causing the issue.

The obvious problem here is the way you fetch your menu from the database.

If for each menu item you do a request to fetch it's submenus, you will quickly end with a lot of requests. An easy solution would be to implement caching, but you could first try to improve the way you query your menu.

A good alternative for mapping trees, instead of referencing the parent item is to use a materialized path. This means that you store in a field a string containing the path to the current item, comma-separated. The real benefit is that you could get a whole tree in only one request using a regexp on the path field :

//get the whole tree of menu 1
SELECT * FROM menuitems WHERE path REGEXP '^1' ORDER BY path;

| ID | Name           | Path   |
| 1  | Hardware       | "1"    |
| 2  | Printers       | "1,1"  |
| 3  | Laser printers | "1,1,1"|
| 4  | Ink printers   | "1,1,2"|
| 5  | Screens        | "1,2"  |
| 6  | Flat Screens   | "1,2,1"|
| 7  | Touch Screens  | "1,2,1"|

Then with a little code, like some recursive function you build your whole navigation.

And consider caching this sort of things