Database Administration
mysql mysql-5.6 performance-tuning greatest-n-per-group
Updated Sat, 07 May 2022 12:18:20 GMT

Optimizing a query that gets the opening balance


I have a query like this

select org, `Balance` from oss_collection_history och2 
  where och2.`DateFrom` = 
    (select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org)

It works, but with my data set it takes about 4 seconds.

The explain output looks like this

id|select_type       |table|partitions|type|possible_keys                     |key|key_len|ref         |rows|filtered|Extra      |
--|------------------|-----|----------|----|----------------------------------|---|-------|------------|----|--------|-----------|
 1|PRIMARY           |och2 |          |ALL |                                  |   |       |            |6651|     100|Using where|
 2|DEPENDENT SUBQUERY|och3 |          |ref |Org,oss_collection_history_Org_IDX|Org|22     |ca1.och2.Org| 443|     100|Using index|

I tried adding the following indeices, but doesn't appear to improve the performance

CREATE INDEX `oss_collection_history_Charges_IDX3` 
ON oss_collection_history (`DateFrom`, `org`);
CREATE INDEX `oss_collection_history_Charges_IDX4` 
ON oss_collection_history (`DateFrom`);
CREATE INDEX `oss_collection_history_Charges_IDX6` 
ON oss_collection_history (`DateFrom`, `org`, `Balance`);



Solution

I created a minimal, complete, and verifiable example for your query.

CREATE TABLE oss_collection_history
(
    org int
    , Balance int
    , DateFrom date
);
INSERT INTO oss_collection_history VALUES (1, 1, '2019-01-01');
INSERT INTO oss_collection_history VALUES (1, 2, '2019-01-02');
INSERT INTO oss_collection_history VALUES (2, 1, '2019-01-03');
INSERT INTO oss_collection_history VALUES (2, 2, '2019-01-04');
CREATE INDEX oss_collection_history_Charges_IDX2 
ON oss_collection_history (org, DateFrom, Balance);

This query returns a single row-per-org, with the associated opening balance, using the index:

SELECT och1.org
    , och1.Balance
    , och1.DateFrom
FROM oss_collection_history och1
INNER JOIN (
    SELECT MIN(och2.DateFrom) AS MinDateFrom
        , och2.org
    FROM oss_collection_history och2 
    GROUP BY och2.org
) t ON och1.org = t.org AND och1.DateFrom = t.MinDateFrom;

Results:

 org   Balance    DateFrom  

   1         1   2019-01-01 
   2         1   2019-01-03 

This fiddle for this is here.





Comments (1)

  • +0 – This is as good as "groupwise-max" gets; it is even better than the new "windowing functions" of MySQL 8.0. — Jan 26, 2020 at 05:24  


External Links

External links referenced by this document: