Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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.

share|improve this question
2  
Injection vulnerabilities need addressing –  Elias Van Ootegem Aug 18 at 8:46

3 Answers 3

up vote 4 down vote accepted

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
share|improve this answer

Each query that you issue incurs some overhead for parsing and planning. As @Phrancis pointed out, some of that overhead can be mitigated by changing them to a stored procedure calls instead. However, you would still be incurring the round-trip performance penalty: your application needs to issue the call and wait for the response to be streamed over the connection before it can issue the next call. If the tree is deep and the network latency is high, those round trips will be significant!

This performance problem is what @DirkPitt was concerned about when he mentioned an important general principle: you should never run a query in a loop, especially where the number of queries issued will scale with the size of the data.

The problem that you are really trying to solve is, how can you efficiently trace the ancestry path from a node to the root of a tree using SQL? That is a common issue, to which there are several possible solutions, all of which require just one query from your PHP code.

Recursive SQL query

Most SQL databases support recursive SQL queries. For example, PostgreSQL has a WITH RECURSIVE syntax that is typical, and the query would look something like this:

WITH RECURSIVE ancestry(user_id, placement_id, depth) AS (
    SELECT user_id, placement_id, 0
        FROM referrals
        WHERE user_id = ?
    UNION ALL
    SELECT parent.user_id, parent.placement_id, child.depth - 1
        FROM referrals AS parent
            INNER JOIN ancestry AS child
                ON parent.user_id = child.placement_id
        WHERE parent.placement_id IS NOT NULL
)
SELECT placement_id
    FROM ancestry
    ORDER BY depth DESC;

Unfortunately, MySQL does not support recursive queries. For ease of development, you may wish to consider switching to a more powerful database. (After all, PostgreSQL is also free.)

Recursive procedure

If you are stuck with MySQL, then you might want to define a stored procedure that performs a recursive traversal to the root, such as the GetAncestry() function written by @RolandoMySQLDBA, adapted to your situation:

DELIMITER $DELIM$
DROP FUNCTION IF EXISTS `GetAncestry` $DELIM$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT placement_id INTO ch FROM referrals WHERE user_id = ch;
        IF ch IS NOT NULL THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $DELIM$
DELIMITER ;

Letting one stored procedure handle the traversal internally is better than having your PHP application do it, since each request-response round trip would incur an additional performance penalty.

A tree representation adapted to relational databases

The MySQL stored procedure is definitely a hack. However, even the recursive SQL query doesn't really fit the relational database model.

If you were willing to change the tree representation to use the Nested Set Model, then the query might look like:

SELECT lft
    FROM referrals
    WHERE ? BETWEEN lft AND rgt
    ORDER BY lft DESC;
share|improve this answer
    
+1, really interesting read about the nested set mode. Thx –  Quamis Aug 21 at 12:39

You should run query only once and then go through the values that has been returned.

Also you can change the query to something like this:

$query = "SELECT placement_id FROM referrals WHERE user_id = $id and placement_id is not null and placement_id <> 2"

Remember to add indexes on each column (user, placement).

share|improve this answer
    
Look at the code, according to the one when 2 occurs value is omitted –  Dirk Pitt Aug 18 at 0:47
    
You're right, my bad –  Phrancis Aug 18 at 0:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.