Re: How can I optimize this

Simon wrote:

Hi, I'm trying to make my code run faster. I have table navigation with fields id, sid, and title. Code looks like this:

Simon, I would presume that your performance problems are simply a result of the number of selects which you execute. That can certainly be improved, but it's all down to the SQL not the php. As I understand it you want to execute $tree->addfolder() for all "parents", (sid=0), children (sid = id of a parent), and grandchildren, (sid = id of a child). This can be done in a single select which joins the main table to itself twice using table aliases for the second and third references to the table. Assuming that not all parents necessarily have children and not all children necessarily have grandchildren then you also need to do a "left outer join" rather than a "natural join". The single select will return one row for every parent/child/grandchild combination, which means you will get the parent information many times over. To make sure you only actually add each parent and each child once, you need to use php to check to see you only processes the first occurrence of each parent and each child as you loop through all the rows. Your code will be something like this:- $query = " SELECT

    parent.id        as parent_id
   ,parent.title     as parent_title
   ,child.id         as child_id
   ,child.title      as child_title
   ,grandchild.id    as grandchild_id

,grandchild.title as grandchild_title FROM text parent left outer join text child on child.sid = parent.id left outer join text grandchild on grandchild.sid = child.id WHERE parent.sid = 0 ORDER BY parent.id ,child.id ,grandchild.id "; // This query must be worth an error check! $result = mysql_query($query_string); if (!$result) { die "Query failed ".mysql_err().$query_string;} $parent_id = 0; while ($myrow = mysql_fetch_array($result) { if ($parent_id != $myrow['parent_id']) {

      $child_tree_id = $tree->add_folder ($root, addslashes($myrow["parent_title"]),

"/cms/text_edit/text_edit.php?a=$myrow[parent_id]");

      $child_id = 0;

      $parent_id = $myrow['parent_id'];

} if ($myrow['child_id'] > 0 and $child_id != $myrow['child_id']) {

      $grandchild_tree_id = $tree->add_folder ($root, addslashes($myrow["child_title"]),

"/cms/text_edit/text_edit.php?a=$child_tree_id");

      $child_id = $myrow['child_id'];

} if ($myrow['grandchild_id'] > 0) {

      $tree->add_folder ($root, addslashes($myrow["grandchild_title"]),

"/cms/text_edit/text_edit.php?a=$grandchild_tree_id"); } } You'll have to check this but it should give you the general idea. The performance improvement is all from having a single select rather than a select for every parent and every child of every parent. Let me know if you hit problems or want more explanations, George P.S. I wouldn't have called your main table "text", since "text" has a special meaning in MySQL, (it's a column type). Something like "sid_entry" might have been safer overall.

I'd like to replace nested while's if possible. TNX $result = mysql_query("SELECT id,title FROM text where sid=0 order by id", $db); while ($myrow = mysql_fetch_array($result)) { $f =$myrow[id]; $myrow["id"] = $tree->add_folder ($root, addslashes($myrow["title"]), "/cms/text_edit/text_edit.php?a=$myrow[id]"); $resulta = mysql_query("SELECT id,title FROM text where sid='$f' order by id", $db); while ($myrowa = mysql_fetch_array($resulta)) { $g =$myrowa[id]; $myrowa["id"] = $tree->add_folder ($myrow["id"], addslashes($myrowa["title"]), "/cms/text_edit/text_edit.php?a=$myrowa[id]"); $resultb = mysql_query("SELECT id,title FROM text where sid='$g' order by id", $db); while ($myrowb = mysql_fetch_array($resultb)) { $tree->add_document ($myrowa["id"], addslashes($myrowb["title"]), "/cms/text_edit/text_edit.php?a=$myrowb[id]"); } } }