如何在一个语句中创建多个语句循环(性能prob/php)

ogq8wdun  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(332)

如何优化以下代码?如何将4个循环减少到一个,它们几乎总是相同的mysql语句。只有where中的步长值总是不同的。
目前代码中这部分的加载时间大约是5-7秒,这太长了。。。每个环路最多可通过200-300次。

// Tank battles heute MySQL
$tday = array();
$result2 = mysqli_query($db, "SELECT * FROM activity_day_account_vehicles WHERE account_id = '$me_id' AND step = '$step'");
while($row_tday = mysqli_fetch_assoc($result2)) {
    $t_id = $row_tday['tank_id'];
    $tday[$t_id] = $row_tday['statistics_battles'];
}
// Tank battles gestern MySQL
$tday2 = array();
$result3 = mysqli_query($db, "SELECT * FROM activity_day_account_vehicles WHERE account_id = '$me_id' AND step = '$step2'");
while($row_tday2 = mysqli_fetch_assoc($result3)) {
    $t_id = $row_tday2['tank_id'];
    $tday2[$t_id] = $row_tday2['statistics_battles'];
}
// Tank battles Woche MySQL
$tweek = array();
$result4 = mysqli_query($db, "SELECT * FROM activity_day_account_vehicles WHERE account_id = '$me_id' AND step = '$step3'");
$menge2 = mysqli_num_rows($result4);
if ($menge2 === 0) {
    $result4 = mysqli_query($db, "SELECT * FROM activity_day_account_vehicles WHERE account_id = '$me_id' ORDER BY step ASC LIMIT $anzahl2");
}
while($row_tweek = mysqli_fetch_assoc($result4)) {
    $t_id = $row_tweek['tank_id'];
    $tweek[$t_id] = $row_tweek['statistics_battles'];
}
// Tank battles Monat MySQL
$tmonth = array();
$result5 = mysqli_query($db, "SELECT * FROM activity_day_account_vehicles WHERE account_id = '$me_id' AND step = '$step4'");
$menge2 = mysqli_num_rows($result5);
if ($menge2 === 0) {
    $result5 = mysqli_query($db, "SELECT * FROM activity_day_account_vehicles WHERE account_id = '$me_id' ORDER BY step ASC LIMIT $anzahl2");
}
while($row_tmonth = mysqli_fetch_assoc($result5)) {
    $t_id = $row_tmonth['tank_id'];
    $tmonth[$t_id] = $row_tmonth['statistics_battles'];
}
rxztt3cl

rxztt3cl1#

根据帐户id和步骤创建索引:

CREATE INDEX activity_day_account_vehicles_001
ON activity_day_account_vehicles (account_id);

CREATE INDEX activity_day_account_vehicles_002
ON activity_day_account_vehicles (step);

相关问题