Performance of fetching DB items individually vs looping with php

Question

Out of curiosity, which of these segments of code would have the faster performance time when implemented on a mass scale?

Let's say we have a table members and we want to fetch their photos.

Method 1

$members = $db->query('SELECT * FROM members ORDER BY id ASC');
foreach($members as $m) 
    $memberIDs[] = $m['id'];

$photos = $db->query('SELECT * FROM photos WHERE member IN'.join(',', $memberIDs).' ORDER BY id ASC');
foreach($photos as $p) {
    // multi_arr_search(search_val, search_column, search_array) returns parent key in multi dimensional array
    $memberArrayKey = multi_arr_search($p['member'], 'id', $members);
    $members[$memberArrayKey]['photos'][] = $p;
}

OR

Method 2

$members = $db->query('SELECT * FROM members ORDER BY id ASC');
foreach($members as $k=>$m) 
    $members[$k]['photos'] = $db->query('SELECT * FROM photos WHERE member='.$m['id'].' ORDER BY id ASC');

Method 1 would result in fewer queries being ran, but requires more PHP work.

Method #3 - Use a JOIN in a single query; but definitely not #2 because there's an overhead per query, and the more queries you execute the slower it will all be — Mark Baker
– Mark Baker, Commented Jun 1, 2016 at 23:19

cpugourou · Accepted Answer · 2016-06-01 23:31:08Z

neither. You are using 15 years old deprecated methods. If you want to go fast, you need PDO prepared statements. @alberto is right. I am pretty sure, you can have one and only sql to perform this but here is a way you might want to consider in case off:

$showError = true;

define("SQLHOST", "127.0.0.1");
define("SQLUSER", "login");
define("SQLPASS", "password");
define("SQLSGBD", "database");

$conn = new PDO('mysql:host=' . SQLHOST . ';dbname=' . SQLSGBD . ';charset=UTF8', SQLUSER, SQLPASS);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql1 = 'SELECT * FROM members ORDER BY id ASC';
$stmt1 = $conn->prepare($sql1);
$sql2 = 'SELECT * FROM photos WHERE member IN ? ORDER BY id ASC';
$stmt2 = $conn->prepare($sql2);

try {
    $stmt1->execute();
    $members = $stmt1->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    if ($showError === true) {
        var_dump("error query 1:" . __LINE__ . "-------" . __FUNCTION__ . "-------" . $e->getMessage());
        exit;
    }
}

if (count($members) !== 0) {
    $memberIDs = array();
    foreach ($members as $m) {
        $memberIDs[] = $m['id'];
    }
    $memberlist = join(',', $memberIDs);

    foreach ($members as $memberArrayKey => $result1) {
        $stmt2->bindParam(1, $memberlist, PDO::PARAM_STR);
        try {
            $stmt2->execute();
            $photos = $stmt2->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            if ($showError === true) {
                var_dump("error query 2:" . __LINE__ . "-------" . __FUNCTION__ . "-------" . $e->getMessage());
                exit;
            }
        }
        if (count($photos) !== 0) {
            $memberArrayKey = multi_arr_search($p['member'], 'id', $members);
            $members[$memberArrayKey]['photos'][] = $p;
        }
    }
}

Alberto · Accepted Answer · 2016-06-01 23:23:08Z

1

Well, hard truth: none is actually scalable. If you're working with large sets of data, PHP will need quite a bit of memory to perform all the operations to link users and photos and will do it in a much more inefficient way than the DB can.

You should have the tool you have that is best at doing these kind of things, which is your DB: the DB should join the users & photos and get you that set.. anything you do differently will mean reduced performance and scalability.

answered Jun 1, 2016 at 23:23

Alberto

8885 silver badges15 bronze badges

Comments

Sergio · Accepted Answer · 2016-06-01 23:24:38Z

1

1- It depends on many factors how are you working with MySQL, if you connect and disconnect connection with the DB at each call. 2- MySQL Server is in the same PHP.

Ideally, connect 1 time mysql to retrieve information with a single Query. And in your case, you can solucuonar with a JOIN. Which it is best to disconnect as quickly as possible and continue with MySQL PHP.

Another recommendation is to not use " * " in the SELECT. Only puts the fields that you go to work. This eat fewer resources because only you use is requested. It is optimal in Memory, CPU, Data via LAN

answered Jun 1, 2016 at 23:24

Sergio

2,4991 gold badge18 silver badges22 bronze badges

Collectives™ on Stack Overflow

Performance of fetching DB items individually vs looping with php

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related