CODEWITHSUNDEEP
phpmysqlrecursionpagination
user3117183
user3117183

Reputation: 49

PHP MySQL recursion slow, how to speed up?

I have a recursive MySQL query that returns about 25000 results, which takes a long time to load.

I would like to know if there is a way to paginate the result or to make things faster.

The code is below:

function getChildIds($id) {    
    echo ' <tr><td> Client No.: </td><td> ';echo $id;echo ' </td></tr> ';

    $sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
    $res = mysql_query($sql_query);
    $ids = Array();
    while($row = mysql_fetch_object($res)){
        if ($row->id) {
            $ids[] = $row->id;
        }
    }

    array_walk($ids, 'getChildIds');
}

Upvotes: 1

Views: 1394

Answers (3)

Toote
Toote

Reputation: 3413

As others mentioned, the main issue is that you are running one MySQL for every user. The overhead for that can be huge so you'd better reduce the amount of queries.

One way is to query all the data in a single query and then process it recursively as you are doing. But, from the looks of it, you just want to get all children of all your users. You can do that in a very simple way with a single MySQL query:

 SELECT
      p.id AS parent,
      GROUP_CONCAT(c.id) AS children
 FROM
     rev_r_clients AS p
     JOIN rev_r_clients AS c ON c.parent_client_id = p.id
 GROUP BY
     p.id

That will provide you with every user and their children in a single query. You can get them in an array using explode afterwards (as GROUP_CONCAT will provide you a comma-separated list).

Based on your comments you just want to create a user tree. Based on the information, this is the code you could use:

# NOTE: I am using mysqli, that is what you should do as well
$res = $mysqli_conn->query(<THE_QUERY_ABOVE>);
$parents = Array();

while($row = $res->fetch_assoc()) {
    $parents[$row['parent']] = explode($row['children'])
}

After that code runs, the $parent array is a mapping of IDs to an array of their children IDs that you can use for whatever you need/want. If an ID is not in the array, it means it has no children.

Upvotes: 3

Kevin Seifert
Kevin Seifert

Reputation: 3582

Also, it will need to pass $limit and $offset numbers in through the next/previous button.

concern: are you handling mysql injection?

$sql_query="select id from rev_r_clients WHERE parent_client_id='$id' limit $limit offset $offset";

Upvotes: 0

jeroen
jeroen

Reputation: 91762

Ideally you would fetch the database results once and use a recursive function on the result - passing the result or part of it around - not make database calls in the recursive function itself.

That would use more memory but it would definitely speed things up.

Upvotes: 1

Related Questions