在PHP中执行多个MySQL查询的更有效方法

Question

我有一个网上商店与数以千计的订单，我写了一些查询，以了解多少钱，每个供应商(品牌)在网站上。我有以下问题，在选定的一年中每个月一次：

$filterJan = "$filterYear-01";
$queryJan = "SELECT price, quantity FROM order_items WHERE productID='$productID' AND timestamp LIKE '%$filterJan%' LIMIT 10000";
$suppliersQueryFilter = mysql_query($queryJan, $connection) or die(mysql_error());

while($rowF = mysql_fetch_assoc($suppliersQueryFilter)) {
    $price = $rowF["price"]*$rowF["quantity"];
    $totalJan = $totalJan+$price;
}

**等等，每月**

它需要很长时间才能加载(至少在60秒以上)，我知道它在任何形状或形式上都是无效的。每个月，这些查询都在数千条记录中进行搜索。是否有一种更有效的方式或将其写到：

( a)将代码数量减少到可能的1次查询b)使增加加载时间更有效

$filterYear包含一年，就像2009年一样。

因此，这个查询所做的是选择在选定的年份(分配给$filterYear)每个月赚多少钱。所以它产生的结果是一个包含Jan，Feb，三月的表.每个月赚多少钱，所以£2345，£2101等等。

Answer 1

为了获得最好的性能，您可能希望向数据库提交类似于以下内容的查询：

SELECT DATE_FORMAT(i.timestamp,'%Y-%m') AS `month`
     , SUM(i.price*i.qty)               AS `total`
  FROM order_items i 
 WHERE i.productID  = 'foo'
   AND i.timestamp >= '2013-01-01'
   AND i.timestamp <  '2013-01-01' + INTERVAL 12 MONTH
 GROUP
    BY DATE_FORMAT(i.timestamp,'%Y-%m')

(这假设timestamp列是MySQL数据类型TIMESTAMP、DATETIME或DATE)

使用不推荐的mysql_接口，您希望避免使用mysql_real_escape_string函数的SQL漏洞。(更好的选择是使用mysqli或PDO接口，并使用绑定占位符的准备语句。)

我们希望时间戳上的谓词位于裸列上，因此MySQL可以为范围扫描操作使用合适的索引，而不是要求对表中的每一行进行全面扫描。

我们还希望使用服务器的强大功能快速派生出一个总计，并只返回总数，而不是检索每个翻转行，并逐个处理这些行(RBAR = row由痛苦的行来处理)

GROUP BY子句和SUM()聚合函数是根据这个结果定制的。

使用mysql_接口，查询将如下所示：

$querytext = "
SELECT DATE_FORMAT(i.timestamp,'%Y-%m') AS `month`
     , SUM(i.price*i.qty)               AS `total`
  FROM order_items i 
 WHERE i.productID  = '" . mysql_real_escape_string($thisProductID) . "'
   AND i.timestamp >= '" . mysql_real_escape_string($filterYear) . "-01-01'
   AND i.timestamp <  '" . mysql_real_escape_string($filterYear) . "-01-01' + 
                      INTERVAL 12 MONTH
 GROUP BY DATE_FORMAT(i.timestamp,'%Y-%m')";

# for debugging
#echo $querytext;

Answer 2

您应该将时间戳存储为一个实际的mysql日期时间值，这将使如下所示

GROUP BY YEAR(timestamp), MONTH(timestamp)
WHERE timestamp BETWEEN $initialtime AND $finaltime

很小的可能性。这样就可以将多个基本上相同的重复查询减少到一个查询。

您可以为此使用派生值，但它比使用本机datetime字段的效率要低：

GROUP BY SUBSTR(timestamp, 0, 4), SUBSTR(timestamp, 6,2)