数组showcasef每页包含20个条目。我在foreach循环中做了3个不同的查询，这是60个查询(只是为了循环，还有额外的查询)。
<?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();
?>
在这里呈现HTML
<?php } ?>
这将是一个严重的性能问题，还是这些特定的查询相对简单?如果我保持列的索引，它是否可以正常执行数百万行(可能)?或者查询可以优化/简化吗?
我是这样得到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();
这里有一些建议。
重用准备好的语句
在循环中创建了三个预处理语句。为什么不只创建语句一次，然后使用多个绑定重用它们呢?
<?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();
使用单个查询来计算行数并计算平均值
可以将第二个和第三个语句合并为一个SQL查询:
SELECT COUNT(r_class) AS cnt, AVG(r_class) AS average
FROM ranks
WHERE r_showcaseid=?
使用单个查询代替foreach循环
使用前面的建议，您可以获得更好的性能。但是您真的确定需要foreach循环吗?
如果您的id是由另一个查询返回的，那么最好使用子查询，而不是foreach循环:
SELECT f_showcaseid, COUNT(f_id)
FROM favourites
WHERE f_showcaseid IN (SELECT id FROM ... WHERE ...)
GROUP BY f_showcaseid
或者您可以为查询提供一个id列表:
SELECT f_showcaseid, COUNT(f_id)
FROM favourites
WHERE f_showcaseid IN (?,?,?,?,?)
GROUP BY f_showcaseid
(你可以动态地创建?如果id的数量不固定)
我认为您可以在单个查询中完成此操作。
类似如下:-
SELECT f_showcaseid, COUNT(f_id), avg(r_class), COUNT(r_class)
FROM ranks WHERE r_showcaseid IN (".implode(',', $showcasef).")
GROUP BY f_showcaseid
当然，要使用参数，你需要做得更优雅一点:-
<?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();
?>