Can MySql nested SP be a bottleneck? We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load. 

It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary tables to pass the data to main SP?

DELIMITER $$

drop procedure if exists `GeoAreaFlattened_Select`;

create procedure `GeoAreaFlattened_Select`(
    _areas MEDIUMTEXT,
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
begin

drop temporary table if exists areas;

-- areas
call CreateAreas(_areas, _comparisonGroup, _parentArea, _areaType);

SELECT
    areas.ID,
    areas.Code,
    areas.Name,
    areas.LevelId,
    GeoAreaLevel.Name AS AreaTypeLabel,
    GeoAreaLevel.Identifier AS AreaTypeIdentifier
FROM
    areas
INNER JOIN
    GeoAreaLevel
ON
    areas.levelid = GeoAreaLevel.id
ORDER BY areas.name ASC;

drop temporary table areas;

end


The nested SP:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
drop procedure if exists `CreateAreas`;

DELIMITER $$

CREATE PROCEDURE `CreateAreas`(
    _areas varchar(21844),
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
BEGIN

    -- create temporary table "areas"
    -- fill with area ids

    create temporary table areas (
        id int not null,
        code varchar(30),
        name varchar(100),
        shortName varchar(100),
        levelid int not null,
        sortOrder int not null,
        key (id)
    );

    -- assumes that only one of the 3 options is valid, areas, comparison group, bounded comparison group

    if (_areas is not null) then

        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder) select id, Code, Name, ShortName, LevelID, 0 from GeoArea where Code in (''', replace(_areas, ',', ''','''), ''')');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    elseif (_comparisonGroup is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
        select GeoAreaID, GeoArea.Code, GeoArea.Name, GeoArea.ShortName, GeoArea.LevelID, SortOrder
        from ComparisonGroupGeoAreaLink
        INNER JOIN
        GeoArea
        ON GeoArea.ID = GeoAreaID
        where ComparisonGroupID = (select id from ComparisonGroup where Identifier = _comparisonGroup)
        and IsMember = 1;

    elseif (_parentArea is not null and _areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
    select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from (select id from GeoArea where Code = _parentArea) as t
        INNER JOIN
        GeoAreaLinkCache c
        ON
        c.ParentAreaID = t.id
        inner join GeoArea a
        on c.ChildAreaID = a.ID
        INNER JOIN
        (select id from GeoAreaLevel where Identifier = _areaType) as l
        ON
        a.LevelID = l.id;        

    elseif (_areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder)
        select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from 
        (select id from GeoAreaLevel where Identifier in (''', replace(_areaType, ',', ''','''), ''')) l
        INNER JOIN
        GeoArea a
        ON
        a.LevelID = l.id');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;


    end if;                 

END
There's nothing inherently wrong with what you are doing, assuming the strings you're splitting up into pieces to form the IN sets are coming from a safe source, but I would have these suggestions for optimization:

There's not an obvious reason to make the call to a separate procedure, as this could all be accomplished in a single procedure, and everything requires some finite amount of time to be done, so writing it as a single proc would save some small amount of time, which helps when you're doing a large amount of the same thing... though I don't think that's the biggest issue, by far.

I suspect the big time killer here is the repeated creation of the temporary table.  On my unimpressive dual 1GHz Opteron test machine, I see dramatically different creation times for temporary tables:

Query OK, 0 rows affected (0.24 sec) # ENGINE=InnoDB (default)
Query OK, 0 rows affected (0.02 sec) # ENGINE=MyISAM
Query OK, 0 rows affected (0.00 sec) # ENGINE=MEMORY


Since you are not using the temporary table to directly update another table, you should be free to use the most lightweight engine that fits the application, even if that means you're mixing transactional and non-transactional storage engines, because you don't need subsequent queries based on the temporary table to actually replicate correctly to downstream slaves and there's nothing here to roll back.  You're creating, inserting, selecting, dropping, and this should not have any impact on replication (otherwise, it might).

You should also evaluate the EXPLAIN output from the queries in the nested proc to be sure they are optimal.  Using derived tables, e.g., SELECT ... FROM (SELECT...) do not really appear to be necessary and may not be the most optimal way to write those queries. 

For example:

insert into areas (id, code, name, shortName, levelid, sortOrder)
select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
    from (select id from GeoArea where Code = _parentArea) as t
    INNER JOIN
    GeoAreaLinkCache c
    ON
    c.ParentAreaID = t.id
    inner join GeoArea a
    on c.ChildAreaID = a.ID
    INNER JOIN
    (select id from GeoAreaLevel where Identifier = _areaType) as l
    ON
    a.LevelID = l.id


...would likely be much better written this way (assuming I've parsed it correctly):

INSERT INTO areas (id, code, name, shortName, levelid, sortOrder)
SELECT a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
  FROM GeoArea t
 INNER JOIN GeoAreaLinkCache c ON c.ParentAreaID = t.id
 INNER JOIN GeoArea a on c.ChildAreaID = a.ID
 INNER JOIN GeoAreaLevel l ON l.id = a.LevelID
 WHERE l.Identifier = _areaType
   AND t.Code = _parentArea;


Avoiding the derived tables potentially gives the optimizer a lot more options when formulating the query plan because derived tables generally limit the usability of indexes.  They may be logically equivalent but aren't equivalent in execution.