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

Question

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

Answer 1

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`)