A child ID getting all parent IDs

Is there any MySQL function that will optimize this code?

function get_parents() {

$ids = array();

while($id) :
    $query = "SELECT placement_id FROM referrals WHERE user_id = $id";
    $query = $this->db->query($query);        
    $result = $query->row();  

    if(!isset($result->placement_id)) :
        break;
    elseif(isset($result->placement_id) && $result->placement_id == 2) :
        break;
    endif;

    $id = $result->placement_id;            
    array_push($ids, $id);

    if($result) :
        continue;
    endif;

    break;
endwhile;

return $ids;

}

The code above will return all parent ID of given user_id, and this will stop if nothing is found. I found this code too slow and heavy load.

My table:

relations table
| id | user_id | placement_id |
| 1 | 2 | NULL |
| 2 | 3 | 2 |
| 3 | 4 | 2 |
| 4 | 5 | 3 |
| 5 | 6 | 4 |
| 6 | 7 | 3 |
| 7 | 8 | 3 |
| 8 | 9 | 3 |
| 9 | 10 | 6 |
| 10 | 11 | 5 |
| 11 | 12 | 6 |
| 12 | 13 | 4 |
| 13 | 14 | 3 |
| 14 | 15 | 9 |
| 15 | 16 | 10 |


user_id is the child and parent is placement_id.

MySQL and caching

From the manual (emphasis added)


The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

To test out, I recreated the table you posted in MySQL Workbench:

use test;
drop table if exists referrals;
create table referrals(
    id int not null primary key,
    user_id int,
    placement_id int
);
insert into referrals(id, user_id, placement_id)
values
(1,          2,         NULL),       
(2,          3,          2),         
(3,          4,          2),         
(4,          5,          3),           
(5,          6,          4),           
(6,          7,          3),           
(7,          8,          3),           
(8,          9,          3),           
(9,          10,         6),           
(10,         11,         5),           
(11,         12,         6),           
(12,         13,         4),           
(13,         14,         3),           
(14,         15,         9),           
(15,         16,        10);

I ran the following query 3 times:

SELECT placement_id FROM referrals WHERE user_id = 3;
-- 1st time 0.002 sec / 0.000 sec
-- 2nd time 0.000 sec / 0.000 sec
-- 3rd time 0.000 sec / 0.000 sec

As you can see, each time you run it with the same user_id it will get it from the cache. But if it's not the same user_id it will start a new query.

Stored statement

Assuming your actual table is much larger than the example provided, you would likely benefit from creating a stored statement, which will retain the execution plan along with the query. To create the statement, just pass this to MySQL once, either from Workbench or from PHP:

delimiter $
    create procedure FindPlacementId (in param_user_id int)
    begin
    SELECT placement_id FROM referrals WHERE user_id = param_user_id;
    end$
delimiter ;

Then this section:

$query = "SELECT placement_id FROM referrals WHERE user_id = $id";

Becomes:

$query = "CALL FindPlacementId ($id);";
-- a few tests:
-- 0.001 sec / 0.000 sec
-- 0.000 sec / 0.000 sec
-- 0.000 sec / 0.000 sec

StackExchange Code Review Q#60315, answer score: 4