MySQL – Performance issue while joining most recent

I have two tables, markets (27 records) and histories (~1.75M records, ~67K per market).

I need to get every market with its most recent histories record. The solutions I tried work but are incredibly slow.

Tables DDL

CREATE TABLE `markets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `base_asset_id` int unsigned NOT NULL,
  `quote_asset_id` int unsigned NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `markets_base_asset_id_quote_asset_id_unique` (`base_asset_id`,`quote_asset_id`),
  KEY `markets_base_asset_id_index` (`base_asset_id`),
  KEY `markets_quote_asset_id_index` (`quote_asset_id`),
  CONSTRAINT `markets_base_asset_id_foreign` FOREIGN KEY (`base_asset_id`) REFERENCES `assets` (`id`),
  CONSTRAINT `markets_quote_asset_id_foreign` FOREIGN KEY (`quote_asset_id`) REFERENCES `assets` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `histories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `market_id` int unsigned NOT NULL,
  `timeframe` enum('1_m','5_m','15_m','30_m','1_H','4_H','6_H','12_H','1_D','1_W','1_M') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `time` int unsigned NOT NULL,
  `is_source` tinyint(1) NOT NULL DEFAULT '0',
  `is_final` tinyint(1) NOT NULL DEFAULT '0',
  `open` decimal(36,18) NOT NULL,
  `high` decimal(36,18) NOT NULL,
  `low` decimal(36,18) NOT NULL,
  `close` decimal(36,18) DEFAULT NULL,
  `volume` decimal(36,18) NOT NULL,
  `ohlc_avg` decimal(36,18) DEFAULT NULL,
  `hl_avg` decimal(36,18) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `histories_market_id_timeframe_time_unique` (`market_id`,`timeframe`,`time`),
  KEY `histories_market_id_index` (`market_id`),
  KEY `histories_timeframe_index` (`timeframe`),
  KEY `histories_time_index` (`time`) USING BTREE,
  CONSTRAINT `histories_market_id_foreign` FOREIGN KEY (`market_id`) REFERENCES `markets` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2334503 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

What I tried

1 – Uncorrelated subquery

I started with this solution since I used it other times, it takes ~7.5s:

SELECT
    m.*,
    h.time,
    h.close
FROM
    markets m
LEFT JOIN (
    SELECT
        market_id,
        MAX(`time`) AS `time`
    FROM
        histories h
    WHERE
        h.is_final = 1
    GROUP BY
        market_id
) latest_history
    ON latest_history.market_id = m.id
LEFT JOIN histories h
    ON h.market_id = m.id
    and h.`time` = latest_history.time;

EXPLAIN result:

+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys                                                                            | key                       | key_len | ref                 | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+
|  1 | PRIMARY     | m          | NULL       | ALL   | NULL                                                                                     | NULL                      | NULL    | NULL                |      27 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>                                                                              | <auto_key0>               | 4       | db_name.m.id        |    1745 |   100.00 | Using index |
|  1 | PRIMARY     | h          | NULL       | ref   | histories_market_id_timeframe_time_unique,histories_market_id_index,histories_time_index | histories_time_index      | 4       | latest_history.time |      26 |   100.00 | Using where |
|  2 | DERIVED     | h          | NULL       | index | histories_market_id_timeframe_time_unique,histories_market_id_index                      | histories_market_id_index | 4       | NULL                | 1744647 |    10.00 | Using where |
+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+

2 – Using WITH

I tried to run the subquery using WITH but with no improvement, still ~7.5s:

WITH latest_history AS (
    SELECT
        market_id,
        MAX(h.`time`) AS `time`
    FROM
        histories h
    WHERE
        h.is_final = 1
    GROUP BY
        market_id
)
SELECT
    m.*,
    h.time,
    h.close
FROM
    markets m
LEFT JOIN latest_history
    ON latest_history.market_id = m.id
LEFT JOIN histories h
    ON h.market_id = m.id
    AND h.`time` = latest_history.time;

EXPLAIN result (identical to the previous one)

+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys                                                                            | key                       | key_len | ref                 | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+
|  1 | PRIMARY     | m          | NULL       | ALL   | NULL                                                                                     | NULL                      | NULL    | NULL                |      27 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>                                                                              | <auto_key0>               | 4       | db_name.m.id        |    1745 |   100.00 | Using index |
|  1 | PRIMARY     | h          | NULL       | ref   | histories_market_id_timeframe_time_unique,histories_market_id_index,histories_time_index | histories_time_index      | 4       | latest_history.time |      26 |   100.00 | Using where |
|  2 | DERIVED     | h          | NULL       | index | histories_market_id_timeframe_time_unique,histories_market_id_index                      | histories_market_id_index | 4       | NULL                | 1744647 |    10.00 | Using where |
+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+

3 – Using WITH and window functions

I then upgraded from 5.7 to 8.0.22 in order to try this other suggested method, which takes even more: ~11s

WITH latest_history AS (
    SELECT
        h.*,
        ROW_NUMBER() OVER (PARTITION BY market_id ORDER BY id DESC) AS rn
    FROM
        histories AS h
    where
        h.is_final = 1
)
SELECT
    m.*,
    latest_history.time,
    latest_history.close
FROM
    markets m
LEFT JOIN latest_history
    ON latest_history.market_id = m.id
    AND latest_history.rn = 1;

EXPLAIN result:

+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+---------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref                | rows    | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+---------+----------+-----------------------------+
|  1 | PRIMARY     | m          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL               |      27 |   100.00 | NULL                        |
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 12      | db_name.m.id,const |    1744 |   100.00 | NULL                        |
|  2 | DERIVED     | h          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL               | 1744647 |    10.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+---------+----------+-----------------------------+

Additional info

I then saw that the subquery alone (MAX and GROUP BY), needed in solutions 1 and 2, takes ~7.5s!

So I believe that there should be something fundamentally wrong with histories structure/indexes, rather than the way I’m joining markets with it. To be clear, that’s the one I’m referring to:

SELECT
    market_id,
    MAX(h.`time`) AS `time`
FROM
    histories h
WHERE
    h.is_final = 1
GROUP BY
    market_id;

EXPLAIN result:

+----+-------------+-------+------------+-------+---------------------------------------------------------------------+---------------------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                                                       | key                       | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------------------------------------------------------+---------------------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | h     | NULL       | index | histories_market_id_timeframe_time_unique,histories_market_id_index | histories_market_id_index | 4       | NULL | 1744647 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------------------------------------------------------------+---------------------------+---------+------+---------+----------+-------------+

time is an int representing a Unix timestamp, id could be used but it does not improve performance.

Of course I tried to look at other questions/posts regarding the performance issue with this group by, but I’m still not able to fix it.

Answer

One alternative would be to filter with a subquery:

select m.*, h.time, h.close
from markets m
left join histories h 
    on  h.market_id = m.id
    and h.time = (
        select max(h1.time) from histories h1 where h1.market_id = m.id and h1.is_final = 1
    )

For performance you want an index on histories(market_id, is_final, time desc).

Since you want just two columns from the histories table, you might also want to consider using two subqueries:

select m.*,
    (select h.time  from history h where h.market_id = m.id and h.is_final = 1 order by h.time desc limit 1) as time,
    (select h.close from history h where h.market_id = m.id and h.is_final = 1 order by h.time desc limit 1) as close
from markets m

The same index would help the query – we could even add close at the end of the index, so: histories(market_id, is_final, time desc, close).

FInally: un very recent versions of MySQL, you could try a lateral join:

select m.*, h.*
from markets m
left join lateral (
    select h.time, h.close
     from history h 
     where h.market_id = m.id and h.is_final = 1 
     order by h.time desc limit 1
) h on true