1

I need to select the oldest(min) due datetime for each InternalOrder and then filter these against some datetime for example NOW(). As you can see in schema image, I designed the schema as one InternalOrder has multiple Deliveries and each Delivery has multiple DeliveryRev(revisions), as I need to have whole trace of inputed data. I always look at the latest revision, if that one is deleted the whole delivery should not be considered.

Table rows:

  • InternalOrder: 2970
  • Delivery: 3258
  • DeliveryRev: 12272

I select the newest DeliveryRev via joins and filter these, which have due datetime field filled and also are not deleted.

I have working query (first one), but it is not exactly, what I need, since I can get the result with InternalOrder, which have oldest(min) due under specified threshold(not selected) and another one, which is over (in the result). In this case I do not want that InternalOrder in the result, which I solved using having instead of where, but this query is taking ages.

Am I doing something wrong with HAVING, or how can I optimize/change to get right result?

schema

This query is fast (40ms), but not getting right result.

SELECT
    s9_.internal_order_id,
    MIN(s10_.due) AS minDue
FROM sales_internal_delivery s9_
    LEFT JOIN sales_internal_deliveryrev s10_
        ON s9_.id = s10_.delivery_id
           AND (s10_.due IS NOT NULL AND s10_.deleted = 0)
    LEFT JOIN sales_internal_deliveryrev s11_
        ON s10_.delivery_id = s11_.delivery_id
           AND s10_.timestamp < s11_.timestamp
WHERE
    s11_.delivery_id IS NULL
    AND s10_.due > NOW()
GROUP BY s9_.internal_order_id
ORDER BY minDue ASC

This query is slow (25s), but the result is alright.

SELECT
    s9_.internal_order_id,
    MIN(s10_.due) AS minDue
FROM sales_internal_delivery s9_
    LEFT JOIN sales_internal_deliveryrev s10_
        ON s9_.id = s10_.delivery_id
           AND (s10_.due IS NOT NULL AND s10_.deleted = 0)
    LEFT JOIN sales_internal_deliveryrev s11_
        ON s10_.delivery_id = s11_.delivery_id
           AND s10_.timestamp < s11_.timestamp
WHERE
    s11_.delivery_id IS NULL
GROUP BY s9_.internal_order_id
HAVING minDue > NOW()
ORDER BY minDue ASC

explain

8
  • It seems like there should be a condition as part of the second join: AND s11_.deleted = 0 otherwise you might be excluding some revisions on the basis that there is a more recent, but deleted, revision.
    – mendosi
    Nov 25, 2016 at 4:02
  • @mendosi The two outer joins together with the IS NULL check in WHERE constitute a technique of finding maximum values without grouping. (The grouping that is in this query is done on a different criterion than the one for which the maximums are being obtained.) AFAICT, the deleted = 0 filter is simply unnecessary for s11_.
    – Andriy M
    Nov 25, 2016 at 7:21
  • Trying to compare the logic of the two queries, I can't see the difference. I realise that some edge cases just may not be very obvious, but it would be much easier to help you if you provided an example for which the queries would produce different output.
    – Andriy M
    Nov 25, 2016 at 8:37
  • @AndriyM I don't think the deleted = 0 is unnecessary, but have a look at this example that I prepared to demonstrate: sqlfiddle.com/#!9/7e4b7e Of course trying to guess the business rules of someone else's database is ultimately futile... if they want it to behave this way then of course it is correct.
    – mendosi
    Nov 25, 2016 at 8:39
  • @AndriyM The difference between the two supplied queries is the comparison of due and NOW(). The first query will return any order which has a due date after today, the second will return only orders where the first due is after today.
    – mendosi
    Nov 25, 2016 at 8:41

1 Answer 1

1

I don't know that the query could be refactored much and still be logically correct. And since I don't have your dataset it is difficult to test possible solutions for performance.

This query should still be logically the same, but may be executed in a more efficient way.

Select sid.internal_order_id,
    Min(sidr.due) As minDue
  From (
    Select delivery_id,
        Max(timestamp) As MaxTimestamp
      From sales_internal_deliveryrev
      Group By delivery_id) As lr
  Join sales_internal_deliveryrev As sidr
    On lr.delivery_id = sidr.delivery_id
    And lr.MaxTimestamp = sidr.timestamp
  Join sales_internal_delivery As sid
    On sidr.delivery_id = sid.id  
  Where sidr.deleted = 0
    And sidr.due Is Not Null
  Group By sid.internal_order_id
  Having minDue > Now()
  Order By minDue Asc; 

Edit

As per discussion about the proper way to handle revisions with deleted = 0 this is an alternate version which does not consider a revision with deleted = 1 when looking for the latest revision.

The WHERE deleted = 0 has been moved into the inner subquery.

Select sid.internal_order_id,
    Min(sidr.due) As minDue
  From (
    Select delivery_id,
        Max(timestamp) As MaxTimestamp
      From sales_internal_deliveryrev
      Where deleted = 0
      Group By delivery_id) As lr
  Join sales_internal_deliveryrev As sidr
    On lr.delivery_id = sidr.delivery_id
    And lr.MaxTimestamp = sidr.timestamp
  Join sales_internal_delivery As sid
    On sidr.delivery_id = sid.id  
  Where sidr.due Is Not Null
  Group By sid.internal_order_id
  Having minDue > '2016-12-07'
  Order By minDue Asc;
8
  • This looks great! It gives correct result and running also in tens of ms instead of secs! I will have to "study", why is that.)
    – solcik
    Nov 25, 2016 at 10:25
  • I just did, thanks a lot, do you mind explaining why it is the order of magnitude faster? Also as you mentioned in comments above, it is designed that I always look at the latest revision, if that one is deleted the whole delivery should not be considered.
    – solcik
    Nov 25, 2016 at 10:38
  • @SoLcIk actually the comments I made about your query and handling where the most recent revision is deleted also apply to this one. I tried to match your logic exactly. I can include alternate version with different handling of deleted = 0.
    – mendosi
    Nov 25, 2016 at 10:41
  • @SoLcIk as for why this is faster, I could say with more certainty if I could run the query on my own machine and examine the execution plans, but I think it is cheaper to find the Max(timestamp) and then join rather than the join with an inequality. Just a feeling.
    – mendosi
    Nov 25, 2016 at 10:45
  • Yeah, but why my first query is fast (using inequality) and second is not? I added sqlfiddle with schema and small sample of data in a comment in question. Thank you very much.
    – solcik
    Nov 25, 2016 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.