所以首先我的 mysql 表是这样定义的:

订单(333000+条记录)

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

订单状态(70条记录)

orders_status_id(pk),
language_id,
orders_status_name

orders_status_history(2220000 多条记录)

  `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`)

所以我想要得到的是下面描述的所有三个选项:

1. 订单必须有 shipping_module pickup_pickup 或 pickup2_pickup2
2. orders_status_history.orders_status_id 必须有以下状态 7, 21, 34, 40
3. 但是 orders_status_history.orders_status_id 不能有以下状态 33,53

解释问题: 假设有 10 个订单，他们在生活中的某个时候有过以下状态:

1. [7, 34, 41,48]
2. [20, 40, 34, 57, 14, 25]
3. [53,7,40,5]
4. [41,25,4]
5. [1,2,3,4]
6. [34,4,33,53,7]
7. [4,21,31,7,8]
8. [78,15,45,40]
9. [53,40,31,21,7]
10. [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

它也像以前一样工作，没有过滤第三个选项

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
                             )

此查询执行时间过长，未能执行

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

这个查询作为第一个查询没有过滤第三个选项也是有效的

所以后来我用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;
}

此代码花费的时间超过 30 米。我 set_time_limit(0);

注意:我只能用Mysql_*因为我们的php版本是4.9

最佳答案

这是获取满足您条件的订单的一种方法:

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 确保您没有最后两个。

如果您需要订单或订单行的详细信息，则需要重新加入这些表。

关于php - Mysql - 执行查询花费太多时间，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/45479664/