How to Improve and speed up this PHP script that uses a nested loop?

Question

I wrote a PHP script that joins two tables to display information in this format:

Jane Doe
Phone: 082 980 9514
Home Loan Applications (Active) - 17/07/2013
Credit Report (Free Report) (Unsubscribed) 12/06/2013

You'll notice that the first and lastname is in the first line of the output, followed by the phone number and then the mailing lists they've been subscribed. Next to the list is the status. Active means that the user has not unsubscribed and is still active while the unsubscribed date is displayed if the user already opted out of the mailing list. Then it is followed by the registration date of the subscriber to a certain mailing list.

Here's the link to the sample tables: Tables

It works as it should but it takes forever to complete since there are about 76,000 records in 1 table and about 100,000 in the other. I would like to ask suggestions on how to optimize the code to speed up the script.

Here's the current code that I've written:

$resultarray = array();

$rs4 = mysqli_query($con1,"SELECT interspire_customfield.subscriberid, interspire_customfield.fname, interspire_customfield.lname, interspire_customfield.phone, emailaddress, subscribedate, unsubscribed, interspire_customfield.listid, listname FROM `interspire_subscriber` INNER JOIN `interspire_customfield` ON interspire_subscriber.subscriberid = interspire_customfield.subscriberid GROUP BY emailaddress");

while($row4 = mysqli_fetch_array($rs4)) {
        $resultarray[] = $row4['subscriberid'].",".$row4['fname'].",".$row4['lname'].",".$row4['phone'].",".$row4['emailaddress'];
}

foreach ($resultarray as $arrlist) {

    $arr = explode(',', $arrlist);
    $sid = $arr[0];
    $frstname = $arr[1];
    $lstname = $arr[2];
    $pnum = $arr[3];
    $emailadd = $arr[4];

    echo $frstname." ".$lstname."<br />";
    echo "Phone: ".$pnum."<br />";

    $rs5 = mysqli_query($con1,"SELECT interspire_customfield.subscriberid, subscribedate, unsubscribed, interspire_customfield.listid, listname FROM interspire_subscriber INNER JOIN interspire_customfield ON interspire_subscriber.subscriberid = interspire_customfield.subscriberid WHERE interspire_subscriber.emailaddress = '$emailadd' GROUP BY interspire_subscriber.listid");

    if (!$rs5) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
}

    while($row5 = mysqli_fetch_array($rs5)) {
        $listname = $row5['listname'];
        $subdate = $row5['subscribedate'];
        $unsub = $row5['unsubscribed'];

        if($unsub == "0"){
            $stat = "Active";
        }else{
            $stat = date('d/m/Y', $unsub);
        }

        $subdt = date('d/m/Y', $subdate);

        echo "* $listname ($stat) - $subdt <br />";
    }
        echo "<br />";
}

seems like it's your mysql beein slow. Check the slow query log — Soundz
– Soundz, Commented Oct 21, 2013 at 8:31
Perform a singular select query with multiple WHERE clauses, instead of doing them implicitly in a loop. — Mark
– Mark, Commented Oct 21, 2013 at 8:39
Why are you calling and overriding your SELECT query in each step of your foreach? — Mark
– Mark, Commented Oct 21, 2013 at 8:40
@maikelsabido another approach would be to store all output data in one variable and do only one echo at the end. But that speed increase might be marginal and you could experience a blank page untill the script is done — Soundz
– Soundz, Commented Oct 21, 2013 at 9:08

Dmitriy Mozgovoy · Accepted Answer · 2013-10-21 09:28:05Z

1

If there are indexes (if not, create it on subscriberid in both fields) in both tables try to use query without JOIN:

SELECT 
ic.subscriberid, 
ic.fname, 
ic.lname, 
ic.phone, 
ic.listid, 
emailaddress, 
subscribedate, 
unsubscribed, 
listname 
FROM `interspire_subscriber` isub, `interspire_customfield` ic
WHERE isub.subscriberid = ic.subscriberid
GROUP BY emailaddress

And use buffer to store strings before printing. You can use string value or array with join() method or just ob_start.

answered Oct 21, 2013 at 9:28

Dmitriy Mozgovoy

463 bronze badges

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

2 Comments

maikelsabido Over a year ago

Thanks. But how can I determine the list down the lists that the subscriber is subscribed to like the example I've given above?

Dmitriy Mozgovoy Over a year ago

Just process data to arrays before listing it. Create tree-like structure: User - Subscribes - ... - Subscribes - subscribe1 - ... - subscribe n `

eggyal · Accepted Answer · 2013-10-21 09:56:36Z

Your intended output necessitates the determination of exactly one set of name and phone number per email address: yet because interspire_subscriber associates multiple subscriberid values with a single emailaddress, the join with interspire_customfield (which contains the name and phone number data) is not 1:1; thus it is possible that an email address may be associated with multiple different names and phone numbers.

Either you must group your output by subscriberid (which presumably will not satisfy your desired result of collating each user's list subscriptions), or you must change the schema: any such change must necessarily involve a one-time determination of which values from interspire_customfield should be associated with each emailaddress. I would then suggest keying interspire_customfield on emailaddress (and dropping listid from that table and subscriberid from one of the tables, depending on whether it should be associated with a subscriber or a subscription).

With those changes made, you could do:

$dbh = new PDO("mysql:dbname=$dbname;charset=utf8", $username, $password);
$qry = $dbh->query('
  SELECT   emailaddress,
           c.fname, c.lname, c.phone,
           s.subscribedate, s.unsubscribed, s.listname
  FROM     interspire_subscriber  AS s
      JOIN interspire_customfield AS c USING (emailaddress)
  ORDER BY emailaddress, s.listid
');

if ($qry) {
  echo '<ol>';

  $row = $qry->fetch();
  while ($row) {
    $current_email = $row['emailaddress'];
    echo '<li>',
           htmlentities($row['fname']),' ',htmlentities($row['lname']),'<br/>',
           'Phone: ',htmlentities($row['phone']),
           '<ul>';
    do {
      $unsub = $row['unsubscribed'];
      echo '<li>',htmlentities($row['listname']),
           ' (',$unsub ? 'Active' : date('d/m/Y', $unsub),')',
           ' - ',date('d/m/Y', $row['subscribedate']),
           '</li>';
    } while ($row = $qry->fetch() and $row['emailaddress'] == $current_email);
    echo   '</ul>',
         '</li>';
  }

  echo '</ol>';
}

As for speed, you should ensure you have defined a composite index on the interspire_subscriber table over (emailaddress, listid); and a UNIQUE index on the interspire_customfield table over (emailaddress).

Whilst we're at it, you may wish to consider whether interspire_subscriber.listname is required (it appears to be denormalised, as it ought to be associated with listid in some other table)? You may also wish to consider storing temporal values using MySQL's native temporal data types instead of integers.

Thank you for your detailed answer/suggestion. I'll try this out. Thank you so much.

Collectives™ on Stack Overflow

How to Improve and speed up this PHP script that uses a nested loop?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related