CREATE FUNCTION `getPicturesCount`(aID bigint) RETURNS bigint( 20 )
return (select sum(isgroup= 3 ) + sum(getPicturesCount(id)) from foto_foto where top = aID);

drop procedure if exists go;
drop function if exists calc;

delimiter |

create procedure go(p_id int, p_isgroup int, in k1 int, out k2 int)
begin
  declare done, l_id, l_isgroup int default  0 ;
  declare cur cursor for 
    select
      id,
      isgroup
    from
      foto_foto
    where
      top = p_id;
  declare continue handler for sqlstate '02000' set done =  1 ;

  set k2 = k1;
  open cur;
    repeat
      fetch cur into l_id, l_isgroup;
      if not done then
        call go(l_id, p_isgroup, k2 + if(l_isgroup = p_isgroup,  1 ,  0 ), k2);
      end if;
    until done end repeat;
  close cur;
end
|

create function calc(p_id int, p_isgroup int) returns int
begin
  declare k int default  0 ;
  call go(p_id, p_isgroup, k, k);
  return k;
end

select calc( 1 ,  3 )

set @max_sp_recursion_depth= 3000 ;

set session max_sp_recursion_depth= 255 

drop procedure if exists scan_proc;

delimiter |

create procedure scan_proc(p_id int, p_isgroup int, out cnt int)
begin
  set cnt =  0 ;
  set @ids = p_id;
  set @isgroup = p_isgroup;
  while not @ids is null do
     set @select_sql = concat(
        'select ',
        '  ( select count(*) from foto_foto where top in (', @ids, ') and isgroup = @isgroup ),',
        '  ( select group_concat(id) from foto_foto where top in (', @ids,') )',
        'into @k, @ids' 
      );  
     prepare scan_cnt from @select_sql;
     execute scan_cnt;
     set cnt = cnt + @k;
  end while;
  deallocate prepare scan_cnt;
end|

call scan_proc( 100693 ,  3 , @a);
select @a;

drop procedure if exists scan_proc;

delimiter |

create procedure scan_proc(p_id int, p_isgroup int, out cnt int)
begin
  set cnt =  0 , @ids = p_id;
  while not @ids is null do
     set @select_sql = concat(
        'select ',
        '  ( select count(*) from foto_foto where top in (', @ids, ') and isgroup = ', p_isgroup, ' ),',
        '  ( select group_concat(id) from foto_foto where top in (', @ids,') )',
        'into @k, @ids' 
      );  
     prepare scan_cnt from @select_sql;
     execute scan_cnt;
     set cnt = cnt + @k;
  end while;
  deallocate prepare scan_cnt;
end|

drop procedure if exists scan_proc;

delimiter |

create procedure scan_proc(p_id int, p_isgroup int, out cnt int)
begin
  set cnt =  0 , @ids = p_id;
    while not @ids is null do
    set @select_sql = concat(
      'select group_concat(id), sum(isgroup = ', p_isgroup, ') '
      'from foto_foto ' 
      'where top in (', @ids,') '
      'into @ids, @k'
    );
    prepare scan_cnt from @select_sql;
    execute scan_cnt;
    set cnt = cnt + ifnull(@k,  0 );
  end while;
  deallocate prepare scan_cnt;
end

drop procedure if exists scan_proc2;

delimiter |

create procedure scan_proc2(p_id int, p_isgroup int, out cnt int)
begin
  declare ids blob default p_id;
  declare k int;

  set session group_concat_max_len =  256  *  1024 , cnt =  0 ;

  while not ids is null do
    select 
      group_concat(id),
      sum(isgroup = p_isgroup)
    from 
      foto_foto 
    where 
      find_in_set(top, ids) 
    into 
      ids, k;
			
    set cnt = cnt + ifnull(k,  0 );
  end while;
end