SQL - return latest of multiple records from large data set

user_6536208 0
Viewed 115

Background

I have a stock_price table that stores historical intra-day stock prices for roughly 1000 stocks. Although the old data is purged regularly, the table regularly has 5M+ records. Structure is loosely:

| id     | stock_id | value | change |  created_at         |
|--------|----------|-------|--------|---------------------|
| 12345  | 1        | 50    | 2.12   | 2020-05-05 17:39:00 |
| 12346  | 2        | 25    | 1.23   | 2020-05-05 17:39:00 |

I regularly need to fetch the latest stock prices for ~20ish stocks at time for an API endpoint. An original implementation of this executed a single query per stock:

select * from stock_prices where stock_id = 1 order by created_at desc limit 1

Part 1: An inefficient query

Somewhat inefficient with 20+ queries, but it worked. The code (Laravel 6) was updated to use the correct relationships (stock hasMany stock_prices), which in turn generated a query like this:

select
  *
from
  `stock_prices`
where
  `stock_prices`.`stock_id` in (1, 2, 3, 4, 5)
order by
  `id` desc

While this saves on queries, it takes 1-2 seconds to run. Running explain shows it's still having to query 50k+ rows at any given time, even with the foreign key index. My next thought was that I'd add a limit to the query to only return the number of rows equal to the number of stocks I'm asking for. Query is now:

select
  *
from
  `stock_prices`
where
  `stock_prices`.`stock_id` in (1, 2, 3, 4, 5)
order by
  `id` desc
limit
  5

Part 2: Query sometimes misses records

Performance is amazing - millisecond-level processing with this. However, it suffers from potentially not returning a price for one/ multiple of the stocks. Since the limit has been added, if any stock has more than one price (row) before the next stock, it will "consume" one of the row counts.

This is a very real scenario as some stocks pull data each minute, others every 15 minutes, etc. So there are cases where that above query, due to the limit will pull multiple rows for one stock and subsequently not return data for others:

| id   | stock_id | value | change | created_at     |
|------|----------|-------|--------|----------------|
| 5000 | 1        | 50    | 0.5    | 5/5/2020 17:00 |
| 5001 | 1        | 51    | 1      | 5/5/2020 17:01 |
| 6001 | 2        | 25    | 2.2    | 5/5/2020 17:00 |
| 6002 | 3        | 35    | 3.2    | 5/5/2020 17:00 |
| 6003 | 4        | 10    | 1.3    | 5/5/2020 17:00 |

In this scenario, you can see that stock_id of 1 has more frequent intervals of data, so when the query was ran, it returned two records for that ID, then continued down the list. After it hit 5 records, it stopped, meaning that stock id of 5 did not have any data returned, although it does exist. As you can imagine, that breaks things down the line in the app when no data was returned.

Part 3: Attempts to solve

  1. The most obvious answer seems to be to add a GROUP BY stock_id as a way to require that I get the same number of results as I'm expected per stock. Unfortunately, this leads me back to Part 1, wherein that query, while it works, takes 1-2 seconds because it ends up having to traverse the same 50k+ rows as it did without the limit previously. This leaves me no better off.

  2. The next thought was to arbitrarily make the LIMIT larger than it needs to be so it can capture all the rows. This is not a predictable solution since the query could be any combination of thousands of stocks that each have different intervals of data available. The most extreme example is stocks that pull daily versus each minute, which means one could have somewhere near 350+ rows before the second stock appears. Multiply that by the number of stocks in one query - say 50, and this still will require querying 15k+ plus rows. Feasible, but not ideal, and potentially not scalable.

Part 4: Suggestions?

Is it such a bad practice to have one API call initiate potentially 50+ DB queries just to get stock price data? Is there some thresehold of LIMIT I should use that minimizes the chances of failure enough to be comfortable? Are there other methods with SQL that would allow me to return the required rows without having to query a large chunk of tables?

Any help appreciated.

mariadb laravel groupwise-maximum mysql database
2 Answers

The fastest method is union all:

(select * from stock_prices where stock_id = 1 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 2 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 3 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 4 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 5 order by created_at desc limit 1)

This can use an index on stock_prices(stock_id, created_at [desc]). Unfortunately, when you use in, the index cannot be used as effectively.

user_11440350

Groupwise-max

SELECT b.*
    FROM ( SELECT stock_id, MAX(created_at) AS created_at
            FROM stock_proces
            GROUP BY stock_id
         ) AS a
    JOIN stock_prices AS b  USING(stock_id, created_at)

Needed:

INDEX(stock_id, created_at)

If you can have two rows for the same stock in the same second, this will give 2 rows. See the link below for alternatives.

If that pair is unique, then make it the PRIMARY KEY and get rid of id; this will help performance, too.

More discussion: http://mysql.rjweb.org/doc.php/groupwise_max#using_an_uncorrelated_subquery

user_17668310
Related