Mysql - too long to execute queries

So my first mysql table is defined like this:

Orders (333,000+ 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 (2,220,000+ records)

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

      

Table

orders_status_history contains different data for each order

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

      

So I'm trying to figure out that all three of the options below are:

  • Orders must have shipping_module

    pickup_pickup or pickup2_pickup2
  • orders_status_history.orders_status_id

    Must have the following status 7, 21, 34, 40
  • But orders_status_history.orders_status_id

    should NOT have the following status 33.53

Explain the problem: Suppose there are 10 orders, and they had the following statuses at one time:

  • [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]

So the orders I want to retrieve are 1,2,7,8 because those orders contain 7,31,21 or 40 and not 53, 33

I've tried the 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 fine as a performance, but it gets the whole result (1200 records), but it doesn't filter out option 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

      

It works the same as the previous one, did not filter the third 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 request took too long to complete and did not miss

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 the same as in the first query, did not filter out the third 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 long over 30m. I set_time_limit(0);

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

+3


source to share


1 answer


Here's one way to get orders that match 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;

      



WHERE

selects only those statuses that you care about. HAVING

makes sure you don't have the last two.

If you want the details of orders or order lines, you need to join these tables.

+2


source







All Articles