PHP MySQL recursion slow, how to speed up?

Question

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');
}

are you printing 25k records in the html???? you have a super browser — Emilio Gort, Dec 21 '13 at 02:22
this should be useful https://datatables.net/release-datatables/examples/data_sources/server_side.html just update to mysqli or pdo and add a delay for the search — Emilio Gort, Dec 21 '13 at 02:28

Toote · Accepted Answer · 2013-12-21T02:39:10.967

3

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.

edited Dec 21 '13 at 02:39

answered Dec 21 '13 at 02:20

Toote

3,323
2
19
25

2

There is no way to "complete the code" when: a) You didn't specify what you are trying to do or get and b) this query would - apparently - make your code obsolete. – Toote Dec 21 '13 at 02:26
am trying to get all the childs and subchilds etc.. for a given id ( tree ) – user3117183 Dec 21 '13 at 02:29
1

That much was clear, what do you want to do with them afterwards? Your code didn't have any `return` – Toote Dec 21 '13 at 02:32
i just echo the id no. calling that function, you will see echo id in second line of the code – user3117183 Dec 21 '13 at 02:33
1

@user3117183 Check here how to build a hierarchical tree: http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree?lq=1 – jeroen Dec 21 '13 at 02:33
jeroen , already have a database built with clients in it thats why i need to modify my code to work with my database tables schema, i know it would be easier if i was creating a new one – user3117183 Dec 21 '13 at 02:37
the question @jeroen linked is also applicable to your situation. It is just changing your field and table names. – Toote Dec 21 '13 at 02:40
1

If you are just echoing the IDs, then I am not sure why you are going to all the trouble to use a recursive function for that. If what you really want is create a user tree, you can use your recursive function on the `$parents` array that my answer leaves you with – Toote Dec 21 '13 at 02:44
@user3117183 Perhaps this is easier and more like what you have already: http://stackoverflow.com/questions/11497202/get-all-child-grandchild-etc-nodes-under-parent-using-php-with-mysql-query-resu – jeroen Dec 21 '13 at 02:44
function subtree($id, $parents) { echo $id; if (isset($parents[$id])) foreach ($parents[$id] as $child) {subtree($child, $parents);}} – Toote Dec 21 '13 at 06:03
Ok got it, needed to change the explode($row['children']) to explode(',',$row['children']) and now its working – user3117183 Dec 21 '13 at 12:25
@toote , any way to paginate the result to 100 per page? – user3117183 Dec 21 '13 at 12:52
depends on what you consider to be "results" as you are not using any filter and not sure how you would break it up – Toote Dec 22 '13 at 19:30
@toote is there a way to sort the result $id's from the function? – user3117183 Dec 31 '13 at 16:08
just add a "ORDER BY" to the query at the end – Toote Dec 31 '13 at 16:22

score 1 · Answer 2 · answered Dec 21 '13 at 02:17

1

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.

answered Dec 21 '13 at 02:17

jeroen

91,079
21
114
132

can you post example ? – user3117183 Dec 21 '13 at 02:30

score 0 · Answer 3 · answered Dec 21 '13 at 02:21

0

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";

answered Dec 21 '13 at 02:21

Kevin Seifert

3,494
1
18
14

PHP MySQL recursion slow, how to speed up?

3 Answers3