CODEWITHSUNDEEP
phpmysql
frosty
frosty

Reputation: 2851

MySQL queries within a foreach loop - performance issue?

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();

Upvotes: 1

Views: 335

Answers (2)

Kickstart
Kickstart

Reputation: 21523

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();

?>

Upvotes: 0

fthiella
fthiella

Reputation: 49069

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)

Upvotes: 3

Related Questions