php - Importing 500 000 categories with Nested set Model with left and right. -

i have 500 000 categories below mysql table structure

create table nested_category ( category_id int auto_increment primary key, name varchar(20) not null, lft int not null, rgt int not null );

here how following left , right http://mikehillyer.com/media//numbered_tree.png

i have table getting row data has below structure.

create table if not exists tbl_categories ( id int(111) not null, category_id int(111) not null, category_path varchar(255) not null, category_name varchar(255) not null, status int(1) not null, tdate datetime not null ) engine=myisam auto_increment=498013 default charset=latin1;

which has data this

id  category_id category_path               category_name   status  tdate 1   381773      top/arts                arts        0   2014-10-07 11:11:05 2   423945      top/arts/animation          animation   0   2014-10-07 11:11:21 3   425085      top/arts/animation/anime        anime       0   2014-10-07 11:11:34 4   425147      top/arts/animation/anime/characters     characters  0   2014-10-07 11:11:35 

i have php script below insert data

echo $sql = "select * tbl_categories status='1' and`category_path` 'top/arts%' order id limit 0,5"; $rescategory = select($sql); for($k=0;$k<count($rescategory);$k++){      $rec_id =$rescategory[$k]["id"];     $category_id =$rescategory[$k]["category_id"];     $category_path =$rescategory[$k]["category_path"];     $category_name =$rescategory[$k]["category_name"];     $friendly_url = getalias($category_name);     //exit;     $cate_path_arr = explode("/",$category_path);     $parent_full_friendly_url = "";     $pad="";     for($i=1;$i<count($cate_path_arr)-1;$i++){         $parent_full_friendly_url .= $pad.getalias($cate_path_arr[$i]);         $pad="/";      }      $full_friendly_url = $parent_full_friendly_url.$pad.$friendly_url;      if($cate_path_arr[count($cate_path_arr)-2]  == "top"){         $parent_category_id = 0;     }else{         $parentcatname  = $cate_path_arr[count($cate_path_arr)-2] ;              $sql = "select id,root_id nested_category title = \"".$parentcatname."\" , full_friendly_url=\"".$parent_full_friendly_url."\"";         $parentcat = select($sql);         $parent_category_id = $parentcat[0]["id"];         $root_id = $parentcat[0]["root_id"];      }      $sql = "insert nested_category set ";     $sql .= "root_id = \"".$root_id."\", ";     $sql .= "title = \"".$category_name."\", ";     $sql .= "category_id = \"".$parent_category_id."\", ";     $sql .= "page_title = \"".$category_name."\", ";     $sql .= "friendly_url = \"".$friendly_url."\", ";     $sql .= "full_friendly_url = \"".$full_friendly_url."\", ";     $sql .= "featured = \"y\", ";     $sql .= "enabled = \"y\" ";     $db->query($sql);     $lastinsid = mysql_insert_id();       $root_id = findrootcategoryid($lastinsid);      if($parent_category_id > 0){         $sql = "update nested_category set count_sub = count_sub+1 id=\"".$root_id."\"";         $db->query($sql);     }else{         $sql = "update nested_category set root_id = \"".$lastinsid."\" id=\"".$lastinsid."\"";         $db->query($sql);     }      rebuildcategorytree($root_id, 1,$lastinsid);      $sql = "update tbl_categories set status = '0',tdate=\"".date("y-m-d h:i:s")."\" id=\"".$rec_id."\"";     $db->query($sql);   }  function getalias($cattitle){      $alias = preg_replace("/(\s){2,}/",'$1',$cattitle);                      $alias = str_replace('-', '_', $alias);              $alias = preg_replace(array('/\s+/','/[^a-za-z0-9\-]/'), array('-','-'), $alias);                $page_alias = trim(strtolower($alias));      return $page_alias; }  function findrootcategoryid($category_id) {             global $db,$dbselect;             $category_id = str_replace("'","",$category_id);             while($category_id != 0) {                 $sql = "select category_id, id nested_category id = $category_id";                                $row = select($sql);                 $category_id = $row[0]["category_id"];                 $root_category_id = $row[0]["id"];             }             return $root_category_id; }   function rebuildcategorytree($category_id, $node_left,$thisid) {             global $db,$dbselect;              if (($category_id > 0) or ($thisid > 0)) {                  $category_id = ($category_id>0)?$category_id:$thisid;                 $node_left = ($node_left>0)?$node_left:1;                 $root_category_id = findrootcategoryid($category_id);                  $sql = 'update nested_category set root_id = '.$root_category_id.' id='.$category_id;                 $db->query($sql);                  $node_right = $node_left+1;                  $sql = 'select id nested_category category_id= '.$category_id;                 $resparentcat = select($sql);                  ($i=0;$i<count($resparentcat);$i++) {                                $node_right = rebuildcategorytree($resparentcat[$i]['id'], $node_right,$thisid);                 }                  $sql = 'update nested_category set `left` = '.$node_left.', `right` = '.$node_right.', root_id = '.$root_category_id.'  id = '.$category_id;                 $db->query($sql);                    return $node_right+1;              }          } ?> 

my question is there way can speed data insertion taking time insert data counting left , right.

i know have posted question first time , need guys give detail side. please let me know valuable comments.

thanks in advance.

don't let yank chain. relatively stable data, nested set far superior adjacency list. here's script used convert 800k+ rows of al ns. typically ran in 6-8 minutes. written in t-sql , i've made changes make work (more or less) schema you've shown it. need work run in mysql. in case, there no need functions or stored procedures. need can plucked directly input table.

i've made 2 changes design. first, sample data looks has identifying value used instead of identity field. second, added setid column maintain each set. finished data this:

id      setid  name        lft rgt 381773  arts   arts        0   11 423945  arts   animation   1   6 425085  arts   anime       2   5 425147  arts   character   3   4 132456  arts   2d          7   10 123457  arts   painting    8   9 381773  crafts crafts      0   7 423945  crafts clay        1   6 425085  crafts structures  2   3 425147  crafts pottery     4   5 

the entire table should not define 1 tree rather forest of trees (i call them "sets" in keeping "nested set" nomenclature) boundary numbering unique within each set. each child of each set has set identifier grouping. boundary numbering restarts @ beginning of each set left boundary value of each set root zero.

the code follows. remember more template final code. ask questions.

create table if not exists nested_category(     id      int not null primary key,     setid   varchar( 20 ) not null,     name    varchar( 20 ) not null,     lft     int not null,     rgt     int not null );  -- written in t-sql syntax modification -- in order. sorry. declare     @catidnew       int,     @catidprev      int = 0,     @rgt            int,     @setid          varchar( 20 ),     @namenew        varchar( 20 ),     @levelnew       int,     @levelprev      int = 0;  declare     @defs table(         lev     int,         id      int,         name    varchar( 20 ),         lft     int     );  declare ss cursor fast_forward     select  category_id, category_name, length( path ) - length( replace( path, '/', '' )) category_level        tbl_categories     --where   category_path 'top/arts%' )  -- uncomment test smaller sample     order category_path;  open ss; fetch next ss @catidnew, @namenew, @levelnew;  -- go ahead , store first record of table insert @defs values( @levelnew, @catidnew, @namenew, 0 ); select  @levelprev  = @levelnew,         @catidprev  = @catidnew,         @setid      = @namenew,         @rgt        = 1; -- second row fetch next ss @catidnew, @namenew, @levelnew;  -- enter row first entry in table variable. we're using table stack. -- read entries cursor, push them "onto" stack until read entry same -- or smaller level previous entry. while @@fetch_status = 0 begin         if @levelnew = 1 begin             -- handle special case when transitioning between sets             insert nested_category( id, setid, name, lft, rgt )             select  id, @setid, name, lft, @rgt +( @levelprev - lev )                @defs               lev <= @levelprev             order lev desc;              -- clear stack             delete @defs;             insert @defs values( @levelnew, @catidnew, @namenew, 0 );              select  @levelprev  = @levelnew,                     @catidprev  = @catidnew,                     @setid      = @namenew,                     @rgt        = 1;         end         else if @levelnew > @levelprev begin             -- new standard next level down last one. save last one.             merge @defs d                 using (select @levelnew) search( lev )                     on d.lev = search.lev                 when matched                     update  set id      = @catidnew,                                 name    = @namenew,                                 lft     = @rgt                 when not matched                     insert values( @levelnew, @catidnew, @namenew, @rgt );             select  @levelprev  = @levelnew,                     @rgt     = @rgt + 1;         end         else begin             -- on same or higher level last one. can write out last 1 new level.             insert nested_category( id, setid, name, lft, rgt )             select  id, @setid, name, lft, @rgt +( @levelprev - lev )                @defs               lev between @levelnew , @levelprev;             -- insert/replace new 1 (in case next 1 lower)             select  @levelprev  = @levelnew,                     @catidprev  = @catidnew,                     @rgt = @rgt + @@rowcount;             merge @defs d                 using (select @levelnew) search( lev )                     on d.lev = search.lev                 when matched                     update  set id      = @namenew,                                 name    = @namenew,                                 lft     = @rgt                 when not matched                     insert values( @levelnew, @catidnew, @namenew, @rgt );             set @rgt = @rgt + 1;         end;      -- read in next row     fetch next ss @catidnew, @namenew, @levelnew; end;  -- there no more entries. purge remaining in stack. insert nested_category( id, setid, name, lft, rgt ) select  id, @setid, name, lft, @rgt +( @levelprev - lev )    @defs   lev <= @levelprev;  close ss; deallocate ss;  -- saving these definitions until after table loaded saves lot of time. alter table nested_category add     constraint  pk_nested_category primary key clustered( id ),     constraint  ix_nested_category_lft unique( setid, lft ),     constraint  ck_nested_categorybounds check( lft < rgt ); alter table nested_category add     constraint  fk_nested_categoryorgid foreign key( setid )references standardorgs( setid ),     constraint  fk_nested_categoryguid foreign key( id )references standardguidxref ( edid ),     constraint  fk_nested_categorysubject foreign key( subjectid )references standardsubjects ( id ),     constraint  fk_nested_categorylevel foreign key( setid, levelid )references standardlevels ( setid, levelid ); create index    ix_nested_categorybounds on nested_category ( setid, lft, rgt ); -- oft-used covering index