Mysql—花费太多时间执行查询。

So first my mysql table are defined like this :

首先我的mysql表是这样定义的

Orders(333000+ records)

订单(333000 +记录)

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

Order_status(70 records)

Order_status(70条记录)

orders_status_id(pk),
language_id,
orders_status_name

orders_status_history(2220000+ records)

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 table contains different status about every orders

orders_status_history表包含每个订单的不同状态

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

So what i am trying to get is that all three option below described:

我想要得到的是下面三个选项

Orders Must have shipping_module pickup_pickup or pickup2_pickup2
订单必须有shipping_module pickup_皮卡或pickup2_pickup2
orders_status_history.orders_status_id Must have following status 7, 21, 34, 40
orders_status_history。orders_status_id必须具有以下状态7、21、34、40
But orders_status_history.orders_status_id Must NOT have following status 33,53
但orders_status_history。orders_status_id不能有以下状态33,53。

Explain Problem : Suppose there are 10 orders, and they have had the followings statuses at some time in their life:

解释问题:假设有10个订单，他们在人生的某个阶段有以下状态:

[7, 34, 41,48]
[7,34岁,41岁的48]
[20, 40, 34, 57, 14, 25]
[20、40、34、57、14、25]
[53,7,40,5]
(53岁,7,40岁,5)
[41,25,4]
(41,25日,4)
[1,2,3,4]
(1、2、3、4)
[34,4,33,53,7]
现年53岁的[34 4 33 7]
[4,21,31,7,8]
31[4,21日,7、8)
[78,15,45,40]
(45 78,15日,40)
[53,40,31,21,7]
[7]53岁,40岁,31日21日
[33,74,54,7,22]
现年54岁的[33,74 7,22)

So Orders that i want to extract are 1,2,7,8 because this orders contain 7,31,21 or 40 and not 53, 33

我想提取的顺序是1 2 7 8因为这个顺序包含7 31 21 40而不是53 33 33

I have tried following queries :

我已经尝试了以下问题:

1.)

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)

This query works ok as performance but it get all result (1200 records) but it didn't filter 3 option

这个查询的性能还可以，但是它得到了所有的结果(1200条记录)，但是它没有过滤3选项

2.)

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

It works also as previous, didnt filter 3rd option

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

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
                             )

This query took too much time to execute and didn't excute

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

4.)

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

This query works as first too queries didn't filter 3rd option

这个查询可以工作，因为first too查询没有过滤第3选项

So later i tried it with php

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

This code was taking too much time more than 30m. I set_time_limit(0);

这个代码花费了超过3000万的时间。我set_time_limit(0);

Note: I can use only Mysql_* because our php version is 4.9

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

1 个解决方案

#1

Here is one method to get the orders that meet your criteria:

这里有一个方法来获得满足您的标准的订单:

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;

The WHERE selects only the statuses that you care about. The HAVING makes sure you don't have the last two.

这里只选择您所关心的状态。拥有确保你没有最后两个。

If you want the details of the orders or order lines, then you need to join those tables back in.

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

#1