MySQL queries within a foreach loop - performance issue?

Question

The array showcasef holds 20 items per page. I do 3 different queries within the foreach loop, which is 60 queries (just for the loop, there's additional queries too).

<?php

foreach($showcasef as $itemf){

      $sf_id = $itemf['sf_id'];
      $sf_url = $itemf['sf_url'];
      $sf_title = $itemf['sf_title'];

      $sf_urltitle = post_slug($sf_title);

      // Fetch number of favs

      $stmt = $conn->prepare("SELECT COUNT(f_id) FROM favourites WHERE f_showcaseid=?");
      $stmt->bind_param("i", $sf_id);
      $stmt->execute();
      $stmt->bind_result($numfFavs);
      $stmt->fetch();
      $stmt->close();

      // Fetch class

      $stmt = $conn->prepare("SELECT avg(r_class) FROM ranks WHERE r_showcaseid=?");
      $stmt->bind_param("i", $sf_id);
      $stmt->execute();
      $stmt->bind_result($sf_class);
      $stmt->fetch();
      $stmt->close();

    // Fetch number of classes

    $stmt = $conn->prepare("SELECT COUNT(r_class) FROM ranks WHERE r_showcaseid=?");
    $stmt->bind_param("i", $sf_id);
    $stmt->execute();
    $stmt->bind_result($numfClasses);
    $stmt->fetch();
    $stmt->close();
?>

Render HTML here

<?php } ?>

Will this be a severe performance issue, or are these particular queries relatively simple? If I keep the columns indexed, should it perform okay with millions of rows (potentially)? Or can the queries be optimized/simplified?

Here's how I get the showcasef:

$stmt = $conn->prepare("SELECT s_id,s_url,s_title FROM showcase WHERE s_userid=? ORDER BY s_date DESC LIMIT $skippingFactor, 20");
$stmt->bind_param("i", $u_id);
$stmt->execute();
$stmt->bind_result($sf_id,$sf_url,$sf_title);

while($stmt->fetch())
{
     $showcasef[] = [
         'sf_id' => $sf_id,
         'sf_url' => $sf_url,
         'sf_title' => $sf_title
     ];
}

$stmt->close();

well - it looks like you could combine those queries into one which would have considerably less of an impact on the server. In fact, you could possibly have just one sql if you prepared the ids ahead of the sql and used something like where r_showcaseid in (id1,id2,id3...) etc and then process that recordset — Professor Abronsius
– Professor Abronsius, Commented Sep 3, 2015 at 8:23

fthiella · Accepted Answer · 2015-09-03 09:58:07Z

3

A few suggestions here.

Reuse prepared statements

You are creating three prepared statements inside the loop. Why don't you create your statements only once, and then reuse them using multiple binds?

<?php

$stmt1 = $conn->prepare("SELECT COUNT(f_id) FROM favourites WHERE f_showcaseid=?");
$stmt1->bind_param("i", $sf_id);
$stmt1->bind_result($numfFavs);
$stmt2 = $conn->prepare("SELECT avg(r_class) FROM ranks WHERE r_showcaseid=?");
$stmt2->bind_param("i", $sf_id);
$stmt2->bind_result($sf_class);
$stmt3 = $conn->prepare("SELECT COUNT(r_class) FROM ranks WHERE r_showcaseid=?");
$stmt3->bind_param("i", $sf_id);
$stmt3->bind_result($numfClasses);

foreach($showcasef as $itemf) {
  $sf_id = ...

  $stmt1->execute();
  $stmt1->fetch();
  /* if the fetch succeedes then $numfFavs will contain the count */

  $stmt2->execute();
  ...

  $stmt3->execute();
  ..
}

$stmt1->close();
$stmt2->close();
$stmt3->close();

Use a single query to Count the rows and calculate the average

You can combine the second and third statement a single SQL query:

SELECT COUNT(r_class) AS cnt, AVG(r_class) AS average
FROM   ranks
WHERE  r_showcaseid=?

Use a single query instead a foreach loop

With the previous suggestions you can get better performances. But are you really sure you need a foreach loop?

If your IDs are returned by another query, instead of a foreach loop is better to use a subquery:

SELECT f_showcaseid, COUNT(f_id)
FROM favourites
WHERE f_showcaseid IN (SELECT id FROM ... WHERE ...)
GROUP BY f_showcaseid

or you can provide a list of IDs to the query:

SELECT f_showcaseid, COUNT(f_id)
FROM favourites
WHERE f_showcaseid IN (?,?,?,?,?)
GROUP BY f_showcaseid

(you can dynamically create the list of ? if the number of IDs is not fixed)

edited Sep 3, 2015 at 9:58

answered Sep 3, 2015 at 8:39

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

5 Comments

syck Over a year ago

Regards for fast and complete answer. (I was still typing myself, but I have nothing to add.)

frosty Over a year ago

I tried to put the first statement out of the loop along with the $stmt->close() at the bottom out of it and it seemed to break the whole thing

frosty Over a year ago

I put both out ($stmt1 and $stmt2) and bound two variables to $stmt2 - $stmt1 seems to work but $stmt2 returns NULL on both bound variables

fthiella Over a year ago

@frosty I updated my answer, because also the bind_param has to be put outside the loop. Can you pls try it? But if possible I would suggest you to use a single query with a subquery

frosty Over a year ago

I added $stmt2->store_result() and it fixed it - yes, I will try that next. Just going through the processes

Kickstart · Accepted Answer · 2015-09-03 10:31:39Z

You could do this in a single query I think.

Something like the following:-

SELECT f_showcaseid, COUNT(f_id), avg(r_class), COUNT(r_class)
FROM ranks WHERE r_showcaseid IN (".implode(',', $showcasef).")
GROUP BY f_showcaseid

Of course, to use parameters you would need to do that a bit more elegantly:-

<?php

    $stmt = $conn->prepare("SELECT f_showcaseid, COUNT(f_id), avg(r_class), COUNT(r_class)
    FROM ranks WHERE r_showcaseid IN (".implode(',', str_split(str_repeat('?', count($showcasef)), 1)).")
    GROUP BY f_showcaseid");

    foreach($showcasef as $itemf)
    {
        $stmt->bind_param("i", $itemf['sf_id']);
    }

    $stmt->execute();
    $stmt->bind_result($numfClasses);
    $stmt->fetch();
    $stmt->close();

?>

Collectives™ on Stack Overflow

MySQL queries within a foreach loop - performance issue?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related