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
source to share
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.
source to share