1

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`);
  • MySQL version ? – Akina Jan 20 at 17:56
  • mysql 5.6 (aurora serverless) – Archimedes Trajano Jan 20 at 18:09
  • I used "value" instead of "balance" for the title so it will be more generic. – Archimedes Trajano Jan 20 at 18:47
2

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.

  • 1
    using non-scientific benchmarks... my old query was 4 seconds this was 33ms – Archimedes Trajano Jan 20 at 18:17
  • That's a win, then! – Max Vernon Jan 20 at 18:37
  • This is as good as "groupwise-max" gets; it is even better than the new "windowing functions" of MySQL 8.0. – Rick James Jan 26 at 5:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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