SQL select数据加速

Question

请帮我生成报告speedy.it加载报告将花费很多时间

for ($i = 0; $i < sizeof($array_time); $i++) {
    $pdf->Cell(.36, .25, $array_time[$i], 'LTRB', 0, "C", 1);
    foreach ($array_day as $days) {
        $datefinal = $datese . $days . $array_time[$i];
        $query2 = "select * from $this->dbName.$this->dbViewTable where DATETIME LIKE '$datefinal%' AND  COMPANY_ID='$companyid' AND SITE_ID='$site_id' AND LOCATION_ID='$location_id'";
        $result = $conn->query($query2);
        $row2 = $result->fetch_assoc();
        $date = $row2['DATETIME'];
        $timec = substr($date, 6, 2) . ":" . substr($date, 8, 2) . ":" . substr($date, 10, 2);
        if ($timec == "::") {
            $imagefill = "images/colors/bg.png";
            $pdf->Cell(.36, .25, "" . $pdf->Image($imagefill, $pdf->GetX(), $pdf->GetY(), .36, .25), 'LTBR', 0, 'C');
        } else {
            $pdf->Cell(.36, .25, $timec, 'LTRB', 0, "C", 1);
        }
    }
    $pdf->Ln();
}

Answer 1

确保数值字段为

INT(5) must be enough

确保datetime字段为

DATETIME

为创建索引

COMPANY_ID
SITE_ID
LOCATION_ID

在查询中不要使用"LIKE“，而要使用

DATE_FORMAT(`datetime`, '%Y-%m-%d') = '$datefinal'