MySQL: Stored Procedure Slow

Question

I have database table where I want to get all children (nth level) of specified parent. For that, I have used answer from here which is working fine.

Here is code:

DELIMITER $$
CREATE PROCEDURE getParents(in_id INT)
  BEGIN
    DROP TEMPORARY TABLE IF EXISTS results;
    DROP TEMPORARY TABLE IF EXISTS temp2;
    DROP TEMPORARY TABLE IF EXISTS temp1;

    CREATE TEMPORARY TABLE temp1 AS
      SELECT DISTINCT * FROM agents WHERE upline_id = in_id;

    CREATE TEMPORARY TABLE results AS
      SELECT * FROM temp1;

    WHILE (SELECT count(*) FROM temp1) DO
      CREATE TEMPORARY TABLE temp2 AS
        SELECT DISTINCT *
        FROM agents
        WHERE upline_id IN (SELECT id FROM temp1);

      INSERT INTO results SELECT * FROM temp2;
      DROP TEMPORARY TABLE IF EXISTS temp1;

      CREATE TEMPORARY TABLE temp1 AS
        SELECT * FROM temp2;
      DROP TEMPORARY TABLE IF EXISTS temp2;

    END WHILE;   

    SELECT * FROM results;

  END $$
DELIMITER ;

Here is how I use it:

call getParents(2060);

Everything is fine but query runs slowly. Table also does not contain more than 10 thousand records.

Is there a way to optimize above stored procedure so that it runs a bit faster ?

I am new to MySQL so I cannot figure out how to optimize this stored procedure. Thanks for the help

Solarflare · Accepted Answer

Update: I probably forgot about the most likely optimization for you: I guess you don't use memory for temporary tables. For mysql >= 5.6 you can set default_tmp_storage_engine=MEMORY in your config (which will have the side effect of making you forget about it when you answer your next stack overflow question), or you can use engine = memory in your query if you have an earlier version or cannot or don't want to change your config. I updated the queries to use memory. If that really was your problem and you were using the hdd for your temp tables, then you might already be happy with your original code and an added engine=memory everywhere, since it will have the biggest effect of all I guess.

Creating and dropping temporary tables are expensive operations. A first optimization would be to create them once and then just to delete the contents, something like this:

DELIMITER $$

CREATE PROCEDURE getParents(in_id INT)
BEGIN

    drop table if exists temp1;
    drop table if exists temp2;
    drop table if exists results; 

    create temporary table temp2 engine=memory as (select id, upline_id from agents where upline_id = in_id); 
    create temporary table results engine=memory as (select id, upline_id from temp2); 
    create temporary table temp1 (id int, upline_id int) engine=memory;

    while (select count(*) from temp2) do 

        insert into temp1 (id, upline_id)
        select a.id, upline_id 
        from agents a
        where a.upline_id in (select id from temp2) ;

        insert into results (id, upline_id)
        select distinct id, upline_id
        from temp1;

        delete from temp2;

        insert into temp2 (id, upline_id)
        select distinct id, upline_id
        from temp1;

        delete from temp1;
    end while;    

    select a.* 
    from results r
    join agents a
    on a.id = r.id;

    drop table if exists temp1;
    drop table if exists temp2;
    drop table if exists results; 

End $$  

DELIMITER ;

Next optimization could be to reduce the number of repetitions by joining ahead (makes the code look more complicated but should be faster) and thus doing several levels at once, and removing one temp table by saving the level n of the child:

DELIMITER $$

CREATE PROCEDURE getParents(in_id INT)
BEGIN

    set @n = 1;

    drop table if exists temp1;
    drop table if exists results; 

    create temporary table results (id int, upline_id int, n int) engine = memory;
    insert into results (id, upline_id, n) 
    select id, upline_id, @n from agents where upline_id = in_id; 

    create temporary table temp1 (id0 int, upline_id0 int, id1 int, upline_id1 int, 
                                  id2 int, upline_id2 int, id3 int, upline_id3 int) engine = memory;

    while (select count(*) from results where n = @n) do 

        insert into temp1 
        select a0.id as id0, a0.upline_id as upline_id0, 
        a1.id as id1, a1.upline_id as upline_id1, 
        a2.id as id2, a2.upline_id as upline_id2, 
        a3.id as id3, a3.upline_id as upline_id3
        from agents a0
        left outer join agents a1
        on a1.upline_id = a0.id
        left outer join agents a2
        on a2.upline_id = a1.id
        left outer join agents a3
        on a3.upline_id = a2.id
        where a0.upline_id in (select id from results where n = @n) ;

        insert into results (id, upline_id, n)
        select distinct id0, upline_id0, @n + 1
        from temp1
        where not id0 is null;
        insert into results (id, upline_id, n)
        select distinct id1, upline_id1, @n + 2
        from temp1
        where not id1 is null;
        insert into results (id, upline_id, n)
        select distinct id2, upline_id2, @n + 3
        from temp1
        where not id2 is null;
        insert into results (id, upline_id, n)
        select distinct id3, upline_id3, @n + 4
        from temp1
        where not id3 is null;

        set @n = @n + 4;

        delete from temp1;

    end while;    

    select a.* 
    from results r
    join agents a
    on a.id = r.id;

    drop table if exists temp1;
    drop table if exists results; 

End $$  

DELIMITER ;

You can increase or decrease the number of joins. More joins will not necessarily be faster, since you might do unused joins if you have sparse data, so you might have to test a little. (It will depend on your data, the number of children per parent, and the depth you most often query from, but 3-4 might be a good starting point. You shouldn't make it too high and should test it for parents with a lot of children/grandchildren.)

But the fastest way to get your results would be to have a look at nested sets, Managing Hierarchical Data in MySQL. It's a little bit to read and to understand, but operations are way faster for nested sets (they are made for exactly your kind of problem in databases). You can have both structures at the same time in the same table (if you might need them at another place, so that's no argument against it), you just have to keep them up-to-date when you change your data. And, well, read a lot first. But it will be worth your while.

MySQL: Stored Procedure Slow

Answers (2)

Related Questions