Slow self join on large table

Viewed 31

I have a table with 70 million rows which currently is structured like:

id    currentDatetime        expiration    delta    strike    bid     ask
1     2022-01-03 09:30:00    2022-01-03    0.05     100       0.85    0.95
2     2022-01-03 11:30:00    2022-01-03    0.05     100       0.65    0.75
3     2022-01-03 13:30:00    2022-01-03    0.09     100       1.00    1.20

For the same expiration and strike, I want to get the delta/bid/ask columns at time A and time B (eg. 09:30:00 and 13:45:00, for example).

I have this query which hopefully does what I'm looking for, but part of the issue is that it's slow to run across 70m rows. I've indexed the columns involved already. Any suggestions?

            SELECT a.id,
            a.currentDatetime,
            a.strike,
            a.expirationDate,
            a.callDelta,a.callBid,a.callAsk,
            b.currentDatetime as dateTimeB,
            b.callDelta as deltaB,
            b.callBid as bidB,
            b.callAsk as askB
            
            FROM ticker a
            INNER JOIN ticker b
            ON (DATE(a.currentDatetime) = DATE(b.currentDatetime)
            AND a.strike = b.strike
            AND a.expirationDate = b.expirationDate)
            
            WHERE (TIME(a.currentDatetime) = :fromTime
            AND TIME(b.currentDatetime) = :toTime)
1 Answers

Add a multicolumn index (currentDatetime, strke, expirationDate) in table.

create index idx_ticket on ticker(currentDatetime, strke, expirationDate);

Related