MySQL的 - 花费过多时间来执行查询

问题描述：

订单（333000+记录）

orders_id(PK), 
customerName, 
CompanyName, 
Status, 
shipping_no, 
shipping_module, 
tracking_no, 
orders_status, 
date_purchased..

ORDER_STATUS（70条记录）

orders_status_id(pk), 
language_id, 
orders_status_name

orders_status_history（2220000+ r ecords）

`orders_status_history_id` int(11) 
    `orders_id` int(11) 
    `orders_status_id` int(5) 
    PRIMARY KEY (`orders_status_history_id`)

orders_status_history表包含每个订单

`orders_status_id` int(11) 
    `orders_status_name` varchar(32) 
    PRIMARY KEY (`orders_status_id`,`language_id`)

那么我试图得到的是不同的状态，下面这三个选项描述：

订单必须有shipping_module pickup_pickup或pickup2_pickup2
orders_status_history.orders_status_id必须有以下状态7，21，34，40
但orders_status_history.orders_status_id不得有下列状态33,53

说明问题：假设有10项目，和他们有过如下的状态，在一些时间在其一生中：

[7，34，41,48]
[20，40，34，57，14，25]
[53,7,40,5]
[41,25,4]
[1,2,3,4]
[34,4,33,53,7]
[4,21,31,7,8]
[78,15,45,40]
[53,40,31,21,7]
[33,74,54,7,22]

所以订单提取是1,2,7,8-因为这个订单包含7,31,21或40，而不是53，33

我曾尝试以下查询：

1）

SELECT * 
FROM orders AS o, orders_status_history AS osh 
WHERE (
     o.shipping_module LIKE '%pickup_pickup%' 
     OR o.shipping_module LIKE '%pickup2_pickup2%' 
    ) 
AND o.orders_id = osh.orders_id 
AND osh.orders_status_id 
IN (7, 21, 31, 40) AND osh.orders_status_id 
NOT IN (33, 53)

此查询工作正常的表现，但它得到的所有结果（1200条记录），但它并没有过滤3选项

2 。）

SELECT * 
FROM orders AS o, orders_status_history AS osh 
WHERE (
     o.shipping_module LIKE '%pickup_pickup%' 
     OR o.shipping_module LIKE '%pickup2_pickup2%' 
    ) 
AND o.orders_id = osh.orders_id 
AND osh.orders_status_id IN (7, 21, 31, 40) 
AND osh.orders_status_id != 33 
AND osh.orders_status_id != 53

它的工作原理也如以前，didnt过滤器3选项

3）

SELECT * 
FROM orders AS o, orders_status_history AS osh 
WHERE (
     o.shipping_module LIKE '%pickup_pickup%' 
     OR o.shipping_module LIKE '%pickup2_pickup2%' 
    ) 
AND o.orders_id = osh.orders_id 
AND osh.orders_status_id IN (7, 21, 31, 40) 
AND osh.orders_status_id NOT IN (
           SELECT so.orders_id 
           FROM orders AS so, orders_status_history AS sosh 
           WHERE (
             so.shipping_module LIKE '%pickup_pickup%' 
             OR so.shipping_module LIKE '%pickup2_pickup2%' 
             ) 
           AND sosh.orders_status_id != 33 
          )

这个查询花费了太多的时间来执行，并没有EXCUTE

4 ）

SELECT * 
FROM orders AS o, orders_status_history AS osh 
WHERE (
     o.shipping_module LIKE '%pickup_pickup%' 
     OR o.shipping_module LIKE '%pickup2_pickup2%' 
    ) 
AND o.orders_id = osh.orders_id 
AND osh.orders_status_id = 7 
AND osh.orders_status_id = 21 
AND osh.orders_status_id = 31 
AND osh.orders_status_id = 40 
AND osh.orders_status_id != 33 
AND osh.orders_status_id != 53

该查询当先过于查询没有过滤选项3

所以后来我用PHP

$sql = "SELECT o.orders_id, osh.orders_status_history_id 
     FROM 
      orders AS o, 
      orders_status_history AS osh 
     WHERE ( 
      o.shipping_module LIKE '%pickup_pickup%' 
      OR o.shipping_module LIKE '%pickup2_pickup2%' 
      ) 
     AND o.orders_id = osh.orders_id 
     AND osh.orders_status_id IN (7, 21, 31, 40) 
     AND osh.orders_status_id != 33 
     AND osh.orders_status_id != 53"; 
$sql = mysql_query($sql); 
while ($row = mysql_fetch_array($sql)){ 
    $row["orders_id"]); 
    if(getOrderStatus($row["orders_id"])) 
     echo "<br/>".$row["orders_id"]; 
} 

function getOrderStatus($order){ 
    $sql = "SELECT orders_status_id FROM orders_status_history WHERE orders_id = ".$order; 
    echo "<br/> Sql Query : ".$sql; 
    $sql = mysql_query($sql); 
    while ($status  = mysql_fetch_array($sql)) 
     if((int)$status["orders_status_id"] == 33 || (int)$status["orders_status_id"] == 53) 
      return false; 
    return true; 
}

此代码是花费过多时间超过30m尝试过。我set_time_limit(0);

注：我只能使用Mysql_ *因为我们的PHP版本是4.9

除了PHP 4.9真的过时了（你应该提醒你的老板升级），你有没有运行'在你的查询中解释......'为表格设置了什么样的指数？ – Paul

'osh.orders_status_id IN（7,21,31,40）AND osh.orders_status_id NOT IN（33,53）''没有任何意义，就好像某物是（7,21,31,40）而不是定义不（33,53）。其他查询以不同的形式呈现。那么你确定你仍然在33，53在你的resluts获得记录？ – pawel7318

我从来没有见过任何人使用PHP 4年...这是严重过时的，完全不安全。此外，它不应该影响查询..因为@ paul提到在查询前面使用'EXPLAIN'。它应该，解释它做了什么，它使用了什么索引......它可能缺少一些 –

答

这里有一个方法来获取订单符合条件：

SELECT o.orders_id 
FROM orders o JOIN 
    orders_status_history osh 
    ON o.orders_id = osh.orders_id 
WHERE (o.shipping_module LIKE '%pickup_pickup%' OR 
     o.shipping_module LIKE '%pickup2_pickup2%' 
    ) AND 
     osh.orders_status_id IN (7, 21, 31, 40, 33, 53) 
GROUP BY o.orders_id 
HAVING SUM(osh.orders_status_id IN (33, 53)) = 0;

的WHERE只选择你关心的状态。 HAVING确保你没有最后两个。

如果你想在订单或订单行的细节，那么你就需要加入这些表回。

MySQL的 - 花费过多时间来执行查询

相关推荐