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)