title: Binary tree height performance on a big tree
body: <p>I'm storing a binary tree on a table in mysql database.
  The table have the columns <em>id</em>, <em>parent</em>, and others that isn't important.
  The columns is self explanatory.</p>
  
  <p>I managed to get height of the tree:</p>
  
  <pre><code>    $depth = 0;
      /* @var $db PDO */
      $stmt = $db-&gt;prepare( "SELECT id FROM wp_members WHERE parent = :parent AND matrix_id = :matrix_id" );
  
      $q = new SplQueue();
      $q-&gt;push( array( 0, 0 ) );
  
      while (!$q-&gt;isEmpty()) {
          $cur = $q-&gt;pop();
  
          $stmt-&gt;bindParam( ':parent', $cur[0] );
          $stmt-&gt;bindParam( ':matrix_id', $matrix_id );
  
          $stmt-&gt;execute();
  
          $ids = $stmt-&gt;fetchAll();
  
          if ($cur[1] &gt; $depth) $depth++;
  
          if (count($ids) &gt; 0) $q-&gt;add( 0, array( $ids[0]['id'], $cur[1] + 1));
          if (count($ids) &gt; 1) $q-&gt;add( 0, array( $ids[1]['id'], $cur[1] + 1));
      }
  
      return $depth;
  </code></pre>
  
  <p>PS: matrix_id => let's call <em>tree id</em></p>
  
  <p>It returns the height of the tree correct, but the problem is that database has more than 18k of nodes, and this takes a lot of time just to get tree height.</p>
  
  <p>What I want to know is what I can do do solve this situation ? it takes more than 60secs to get height of the tree.</p>