How to optimise large mySQL within a foreach loop in PHP?

Question

I have a function that gets user details and returns an associative array consisting of arrays of each user with its related data. My function works except that it doesn't work as well when it has to fetch large number of rows from mySQL.

function function_name($DB, $id)
{
    //Prepare, bind and execute statements
    //Returns one value or an array
}

function main_function($DB, $id_list)
{
    foreach($id_list as $user_id)
    {
        //Calls function_name
        $data = function_name($DB, $user_id);
    }

    //Returns a nested associative array
}

I have been told that I should move the bind param statement outside of the foreach loop in my case but I have tried and I keep getting error "MySQL has went away" message. How can I optimise querying from mysql when I could potentially be querying 10,0000 id at one time?

Please refer to the code snippet below for detailed explanation.

function getUserEvent($DB_3308, $user_id) 
{
    $user_event = array ();

    $sql_get_user_event = "SELECT * FROM user_event WHERE user_id = ?";

    $statement_user_event = $DB_PUMA_3306->link->prepare ( $sql_get_user_event);
    $statement_user_event ->bind_param ( "s", $user_id );
    $statement_user_event ->execute ();

    if ($rs_user_event = $statement_user_event->get_result ()) 
    {
        while ( $row = $rs_user_event->fetch_assoc () ) 
        {
            $user_event [] = $row;
        }
    }

    return $user_event;
}

function getUserDetails($DB_3306, $DB_3308, $user_list)
{
    $user_details = array ();

    foreach ( $user_list as $user_id )
    {
        $temp = array ();    
        $user_personal = null;
        $user_event = null;

        $user_personal = getUserContact ( $DB_3306, $user_id );
        $user_event = getUserEvent( $DB_3308, $userid );

        $temp ['user_id'] = $user_id;
        $temp ['full_name'] = $user_personal ['full_name'];
        $temp ['tel_no'] = $user_personal ['tel_no'];
        $temp ['email'] = $user_personal ['email'];
        $temp ['events'] = $user_event ;


        $user_details [] = $temp;
    }

    return $user_details;
}

@AbhikChakraborty - I am sorry I am not sure what you mean by pagination. — Cryssie
– Cryssie, Commented Dec 5, 2014 at 7:16
I meant if you have a display page where you are listing all the users from the DB, you can choose to display say 100 first time with pagination, check this link what did I mean by pagination datatables.net/examples/basic_init/alt_pagination.html — Abhik Chakraborty
– Abhik Chakraborty, Commented Dec 5, 2014 at 7:22
It is for display. But does it work when I have a lot more calculation stuff done not shown here? For e.g. calculating total number of results, comparing two arrays to get total number of intersect, etc? — Cryssie
– Cryssie, Commented Dec 5, 2014 at 7:24
ofcource it works and its never been a good idea to load all users at the same time, and more over storing in an array will eat up all memory. You can find many pagination scripts on PHP and can customize the way you want. — Abhik Chakraborty
– Abhik Chakraborty, Commented Dec 5, 2014 at 7:27

Vinoth Babu · Accepted Answer · 2014-12-05 07:21:04Z

2

Why can't you get some 50 or 100 userID in array before fetching that from database and fetch it in bulk to reduce more query load?

$implodedUserIDs = implode(',', $userIDs);
$query = "SELECT * FROM user_event WHERE user_id IN ($implodedUserIDs)";

It will reduce some load. Also you can give some sleep in every load. Just try to optimize your code as much as possible. :)

answered Dec 5, 2014 at 7:21

Vinoth Babu

6,82510 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cryssie Over a year ago

This was one the things I wanted to try but I have been told that using "SELECT .. WHERE...IN" will increase the workload for mysql. Am I wrong to understand that?

Vinoth Babu Over a year ago

If you use that it will reduce the number of query. please try that once.. you can see the diff in loading time..

TRT 1968 Over a year ago

If you want to get the userIDs into an array quickly try this: $query = "SELECT user_id FROM user_list_tmp"; $result = $mysqli->query($query); $userIDs = array_column($result->fetch_all(), 0);

Kickstart · Accepted Answer · 2014-12-05 10:44:02Z

You appear to be looping around (potentially) 10000 users and for each one performing at least 2 queries. Each query has a small over head to parse it, etc, and hence with a large number of queries this can rapidly add up.

I would suggest that if possible you merge the 2 queries together, doing a join to get both the users contact details and the user event details.

I would also suggest that you perform this single query once in total for all user ids rather than once per user id. Normally this would be easy to do using IN with a list of user ids, but with 10000 this is not really viable. As such generate a temp table containing your list of user ids.

Very roughly (and making assumptions on your database class and on your actual data) something like this:-

function getUserDetails($DB_3306, $DB_3308, $user_list)
{

    $sql = 'CREATE TEMPORARY TABLE user_list_tmp
            (
                user_id INT
            )';

    $DB_3306->execute($sql);

    $user_list_split = array_chunk($user_list, 250);

    foreach($user_list_split as $user_list_split_chunk);
    {
        $sql = 'INSERT INTO user_list_tmp (user_id) VALUES ('.implode('),(', $user_list_split_chunk).')';
        $DB_3306->execute($sql);
    }

    $sql = "SELECT a.user_id, b.full_name, b.tel_no, b.email, c.event_id
            FROM user_list_tmp a
            INNER JOIN user_contact b
            ON a.user_id = b.user_id
            LEFT OUTER JOIN user_event c
            ON a.user_id = c.userid
            WHERE user_id = ?
            ORDER BY a.user_id, c.event_id";

    $statement_user_event = $DB_3306->link->prepare ( $sql);
    $statement_user_event ->execute ();

    $user_details = array();

    if ($rs_details = $statement_user_event->get_result ()) 
    {
        while ( $row = $rs_details->fetch_assoc () ) 
        {
            $user_details[$row['user_id']]['user_id'] = $row['user_id'];
            $user_details[$row['user_id']]['full_name'] = $row['full_name'];
            $user_details[$row['user_id']]['tel_no'] = $row['tel_no'];
            $user_details[$row['user_id']]['email'] = $row['email'];
            $user_details[$row['user_id']]['events'][] = $row['event_id'];
        }
    }
    return $user_details;
}

This takes you array of passed user ids, chunks it into arrays of 250 and inserts them into a temp table (I tend to insert in batches of 250 as a reasonable balance between a readable and fast insert statement and performing a min number of separate statements - you may chose to split it into larger or smaller chunks).

It then performs a single query that joins the temp table against the user_contact table and left joins it against the user_event table. Each user will return multiple rows, one for each even (but still one row if no events). It is putting these into an array, and I have cheated a bit here by using the user_id as the key of the array. So for the first row for the user id it will save the details for the user, and on any subsequent rows for the user (for further events) the user details will just over write themselves. The events details are just put into the next array member of the events array for that user.

Collectives™ on Stack Overflow

How to optimise large mySQL within a foreach loop in PHP?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related