CODEWITHSUNDEEP
mysql
h2ooooooo
h2ooooooo

Reputation: 39540

How to get the most recent entry that's older than 15 minutes ago?

The problem:

We're getting stock prices and trades from a provider, and to speed things up we cache the trades as they come in (1 trade per second per stock is not a lot). We've got around 2,000 stocks, so technically, we're expecting as much as 120,000 trades per minute (2,000 * 60). Now, these prices are realtime, but to avoid paying licensing fees to show these data to the customer we need to show the prices delayed with 15 minutes. (We need the realtime prices internally, which is why we've bought and pay for them (they are NOT cheap!))

I feel like I've tried everything, and I've run into an uncountable number of problems.

Things I've tried:

1:

Run a cronjob every 15 seconds that runs a query that checks what the trade for the stock, more than 15 minutes ago, had for an ID (for joins):

SELECT
    MAX(`time`) as `max_time`,
    `stock_id`
FROM
    `stocks_trades`
WHERE
    `time` <= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
AND
    `time` > '0000-00-00 00:00:00'
GROUP BY
    `stock_id`

This works very fast - 1.8 seconds with ~2,000,000 rows, but the following is very slow:

SELECT
    st.id,
    st.stock_id
FROM
    (
        SELECT
            MAX(`time`) as `max_time`,
            `stock_id`
        FROM
            `stocks_trades`
        WHERE
            `time` <= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
        AND
            `time` > '0000-00-00 00:00:00'
        GROUP BY
            `stock_id`
    ) as `tmp`
INNER JOIN
    `stocks_trades` as `st`
ON
    (tmp.max_time = st.time AND tmp.stock_id = st.stock_id)
GROUP BY
    `stock_id`

..that takes ~180-200 seconds, which is WAY too slow. There's an index on both time and stock_id (indiviudally).

2:

Switch between InnoDB/MyISAM. I'd think I would need InnoDB (we're inserting A LOT of rows from multiple threads, we don't want to block between each insert) - InnoDB seems faster at inserting, but WAY slower at reading (we require both, obviously).

3:

Optimize tables every day. Still slow.

What I think might help:

  1. Using ints instead of DateTime. Perhaps (since the markets are open from 9-22) keep a custom int time, which would be "seconds since 9 o'clock this morning" and use the same method as above (it seems to make some difference, albeit not a lot)
  2. Use MEMORY instead of InnoDB - probably not the best idea with ~18,000,000 rows per 15 minutes, even though we have plenty of memory
  3. Save price/stockID/time in memory in our application receiving the prices (I don't see how this would be any different than using MEMORY, except my code probably will be worse than MySQL's own code)
  4. Keep deleting trades older than 15 minutes in hopes that it'll speed up the queries
  5. Some magic query that I just haven't thought of that uses the indexes perfectly and does magical things
  6. Give up and kill one self after spending ~12 hours on trying to wrap my head around this and different solutions

Upvotes: 6

Views: 234

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270697

Since your are joining against your subquery on two columns (stock_id, time), MySQL ought to be able to make use of a compound index across both of them, while it cannot make use of either of the individual column indices you already have.

ALTER TABLE `stocks_trades` ADD INDEX `idx_stock_id_time` (`stock_id`, `time`)

Upvotes: 1

Sergio Ayestar&#225;n
Sergio Ayestar&#225;n

Reputation: 5920

What happens if you run something like this? Try to change it to include the proper column name for the price if needed:

SELECT st.id, st.stock_id
FROM stock_trades as st
WHERE   time <= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
AND time > DATE_SUB(NOW(), INTERVAL 45 MINUTE)
AND not exists (select 1 from stock_trades as st2 where st2.time <= DATE_SUB(NOW(), INTERVAL 15 MINUTE) and st2.stock_id = st.stock_id and st2.time > st.time)

hope it helps!

Upvotes: 0

smurtagh
smurtagh

Reputation: 529

Assuming your have an auto incrementing id as the primary key on stock_trades (call it stock_trade_id), you could select the max('stock_trade_id') as 'last_id' on the inner query and then do an inner join on the 'last_id' = 'stock_trade_id' so you will be joining on your PK and have no date compares on your main join.

SELECT
st.id,
st.stock_id
FROM
(
    SELECT
        MAX(`stock_trade_id`) as `last_id`,
        `stock_id`
    FROM
        `stocks_trades`
    WHERE
        `time` <= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
    AND
        `time` > '0000-00-00 00:00:00'
    GROUP BY
        `stock_id`
) as `tmp`
INNER JOIN
    `stocks_trades` as `st`
ON
   (tmp.last_id = st.stock_trade_id)
GROUP BY
   `stock_id`

Upvotes: 0

Related Questions