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.
Advertisement
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