php - 为什么我的几个月和一年的 SQL 查询这么慢？

Question

我的数据库表中有大约 4000 个日期。从这些数据中，我需要在数组中具有相同month和的总计数和yearyearmonthtotal result。数组必须如下所示：$ year ['year'] [strftime ('% Y', $ application ['crdate'])] ['month'] [$ i] = intval ($ application ['erg']);

像那个例子一样渲染：

[year, month jan (01), month feb (02), ...]

有数据：

[2016, 0, 0, 0, 4, 0, 9, 0, 0, 0, 0, 0, 2]

我有以下代码来执行此操作，但它需要永远加载 4000 条记录。为什么这么慢，我该如何加快速度？

$year = array();
$where_month = false;
if($this->request->hasArgument('time')) {
    $von = $this->request->getArgument('time')['von'];
    $bis = $this->request->getArgument('time')['bis'];
    $von_jahr = strftime('%Y',strtotime($von));
    $bis_jahr = strftime('%Y',strtotime($bis));
    $von_englisch_format = strftime('%Y-%m-%d',strtotime($von));
    $bis_englisch_format = strftime('%Y-%m-%d',strtotime($bis));
    $von_stamp = strtotime($von);
    $bis_stamp = strtotime($bis);
    $where_month = true;
}

for($i = 1; $i <= 12; $i++) {
    if ($i <= 9) {
        $i = '0' . $i;
    }
    $select_fields = 'T1.*';
    $from_table = ' datas T1';
    if($where_month) {
        $where_clause = 'DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%Y") >= "'.$von_jahr.'" AND DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%Y") <= "'.$bis_jahr.'"';
    } else {
        $where_clause = '';
    }
    $groupBy = '';
    $orderBy = 'crdate ASC';
    $limit = '';
    $erg = 0;
    $result = $GLOBALS['TYPO3_DB']->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
    foreach($result as $bewerbung) {
        $year['jahr'][strftime('%Y', $bewerbung['crdate'])]['monat'][$i] = 0;

        $select_fields = '*, count(*) AS erg';
        $from_table = ' datas T1';
        if($where_month == false) {
            $where_clause = 'DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%Y") = "' . strftime('%Y', $bewerbung['crdate']) . '" AND DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "' . $i . '"';
        } else {
            $where_clause = '(DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%Y-%m-%d") BETWEEN "'.$von_englisch_format.'" AND "'.$bis_englisch_format.'") AND DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%Y") = "' . strftime('%Y', $bewerbung['crdate']) . '" AND DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "' . $i . '"';
        }
        $groupBy = '';
        $orderBy = 'crdate ASC';
        $limit = '';
        $result = $GLOBALS['TYPO3_DB']->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
        foreach ($result as $bewerbung) {
            if (intval($bewerbung['erg']) >= 1) {
                $year['jahr'][strftime('%Y', $bewerbung['crdate'])]['monat'][$i] = intval($bewerbung['erg']);
            }
        }
    }
}

标签： phpsqlarrays

Answer 1

我的新解决方案如下所示：

 $select_fields = 'T1.*, DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%Y") AS "jahr", 
    COUNT(        CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "01"
        THEN 1
        ELSE NULL
    END
        ) AS "januar",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "02"
        THEN 1
        ELSE NULL
    END
) AS "februar",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "03"
        THEN 1
        ELSE NULL
    END
) AS "maerz",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "04"
        THEN 1
        ELSE NULL
    END
) AS "april",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "05"
        THEN 1
        ELSE NULL
    END
) AS "mai",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "06"
        THEN 1
        ELSE NULL
    END
) AS "juni",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "07"
        THEN 1
        ELSE NULL
    END
) AS "juli",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "08"
        THEN 1
        ELSE NULL
    END
) AS "august",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "09"
        THEN 1
        ELSE NULL
    END
) AS "september",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "10"
        THEN 1
        ELSE NULL
    END
) AS "oktober",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "11"
        THEN 1
        ELSE NULL
    END
) AS "november",
COUNT(
    CASE
        WHEN DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%m") = "12"
        THEN 1
        ELSE NULL
    END
) AS "dezember"';
    $from_table = ' tx_lemmbewerberportal_domain_model_bewerbungen T1';
    $where_clause = '';
    $groupBy = 'DATE_FORMAT(FROM_UNIXTIME(T1.crdate), "%Y")';
    $orderBy = 'crdate ASC';
    $limit = '';
    $erg = 0;
    $result = $GLOBALS['TYPO3_DB']->exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
    $i = 0;
    foreach ($result as $row) {
        $year[$i]['jahr'] = $row['jahr'];
        $year[$i]['01'] = $row['januar'];
        $year[$i]['02'] = $row['februar'];
        $year[$i]['03'] = $row['maerz'];
        $year[$i]['04'] = $row['april'];
        $year[$i]['05'] = $row['mai'];
        $year[$i]['06'] = $row['juni'];
        $year[$i]['07'] = $row['juli'];
        $year[$i]['08'] = $row['august'];
        $year[$i]['09'] = $row['september'];
        $year[$i]['10'] = $row['oktober'];
        $year[$i]['11'] = $row['november'];
        $year[$i]['12'] = $row['dezember'];
        $i++;
    }
    return $year;

在一秒钟内，数据被加载。还是非常感谢。:-)