0

Here's how I'm getting my data from database. It is a 3 level hierarchy. Parent, child and children of child.

page_id | parent_id 
    1         0          
    2         1
    3         0
    4         3

The top data shows 0 as the parent.

$id = 0;
public function getHierarchy($id)
   {
       $Pages = new Pages();
       $arr = array();
       $result = $Pages->getParent($id);

       foreach($result as $p){
           $arr[] = array(
               'title' => $p['title'],
               'children' => $this->getHierarchy($p['page_id']),
               );
       }
       return $arr;
   }

So far I'm getting data but it's kinda slow. The browser loads so long before showing the data. How can I make my code faster so it does not take long for the browser to load data?

Thanks.

Gumbo
  • 643,351
  • 109
  • 780
  • 844
Defyleiti
  • 535
  • 1
  • 7
  • 23
  • 1
    Have a look at [What are the Options for Storing Hierarchical Data in a Relational Database?](http://stackoverflow.com/q/4048151/53114) – Gumbo Mar 08 '14 at 12:37

1 Answers1

1

You have multiple options to speed it up:

  1. Select all rows from table and do the recursion only on the PHP side. It's only an option if there are not too many rows in table.
  2. You can self join the table. This is a nice option if you know that there are no more levels of hierarchy in the future. Look at the post: http://blog.richardknop.com/2009/05/adjacency-list-model/
  3. Use nested sets: http://en.wikipedia.org/wiki/Nested_set_model
  4. Or my favorite: Closure Table
  5. Use some kind of caching. You could cache the db queries or the whole tree menu html or the PHP array for example.

Caching example:

Caching you can do in normal files or use specialized api's like memcache, memcached or apc. Usually you have a class with 3 basic methods. An interface could look like:

interface ICache
{
    public function get($key);
    public function set($key, $value, $lifetime = false);
    public function delete($key);
}

You can use it like:

public function getHierarchy($id)
{
    $cached = $this->cache->get($id);
    if (false !== $cached) {
        return $cached;
    }

    // run another method where you build the tree ($arr)

    $this->cache->set($id, $arr, 3600);

    return $arr;
}

In the example the tree will be cached for one hour. You also could set unlimited lifetime and delete the key if you insert another item to the db.

bitWorking
  • 12,485
  • 1
  • 32
  • 38
  • I think I'll try number 5. How can i do that in php? caching. Can u give me a sample? – Defyleiti Mar 08 '14 at 12:51
  • Thanks for your help I'll try this one. What if there is a change in database how can i detect it if the data is cached? – Defyleiti Mar 08 '14 at 13:10
  • Look at the following sources for example cache implementations: [https://code.google.com/p/google-api-php-client/source/browse/trunk/src/cache/?r=474](https://code.google.com/p/google-api-php-client/source/browse/trunk/src/cache/?r=474). What I don't like is that the `$expiration` is set in the `get` method. But try yourself.. – bitWorking Mar 08 '14 at 13:18
  • If you change the database you have to delete all cached keys that are responsible for the tree. You can choose a naming convention for your keys for example: `$cache->set('tree'.$id, $arr);` – bitWorking Mar 08 '14 at 13:26
  • Or in case of the file caching you can save all the keys in a special folder and clear the whole folder on a db change. – bitWorking Mar 08 '14 at 13:28