Mysql – Optimizing a query that gets the opening balance

greatest-n-per-groupMySQLmysql-5.6performance-tuning

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`);

Best Answer

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.

Related Question