Mysql - Taking too much time to execute queries

Question

So first my mysql table are defined like this :

Orders(333000+ records)

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

Order_status(70 records)

orders_status_id(pk),
language_id,
orders_status_name

orders_status_history(2220000+ records)

  `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_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:

1. Orders Must have shipping_module pickup_pickup or pickup2_pickup2
2. orders_status_history.orders_status_id Must have following status 7, 21, 34, 40
3. But orders_status_history.orders_status_id Must NOT have following status 33,53

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

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]

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

I have tried following queries :

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

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

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

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

So later i tried it with 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);

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

Answer 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.