My answer is similar to the one given for this other question.
If you are mainly using the data for backtesting, there's very little reason to store the data in a MySQL database. The data generally follows a write-once, read-many (WORM) pattern, with no need for ACID semantics. You also don't have to enforce referential integrity on most of the data. If you want to do so for the parts relating to the instrument definitions (e.g. underlying, strike price, expiration date, put/call), you can split out only the definitions and put those in a MySQL database.
Generally I see people storing the data in a MySQL database only because of (1) limited experience with other storage formats and tools, (2) excess exposure to MySQL because of non-financial or web development background. It is reasonable to start with MySQL because it is a tool you're familiar with, but once you start running into performance constraints here, it would be one of the most obvious pieces in your stack to optimize away.
If you still want to use a DBMS for this use case so you don't have to write custom routines for processing the data, I would suggest something like Clickhouse, which I've had good experience with. It doesn't have the cost or licensing limitations that come with kdb or Vertica, and is definitely more suited for the use case than InfluxDB. ~2B+ records is actually fairly small, as there are production Clickhouse deployments that run queries and materialized views on a dataset that is growing by 6 million entries per second.
Another observation is that you're storing all of that data in 1 single, large table with 1 field identifying the ticker. This is often an anti-pattern for this use case because:
- Options data is fairly sparse - there are a large number of tickers over which most have very few entries.
- You almost never have a strategy that needs to backtest over all or most of the tickers at once.
Often, it will make more sense to split the data into smaller tables, e.g. 1 per instrument. On paper this will seem unintuitive because you have to pay linear time to merge the smaller sorted tables back together and recompose the set of tickers. However it is generally more efficient than keeping the data in 1 large table and filtering/discarding entries from the table. This is driven by practical considerations:
- The number of entries of interest is small relative to the total number of entries.
- You're saving significant I/O cost which will dominate the processing time for this type of workload.
Of course, 1 "table" per instrument could be hard to administrate whether in a DBMS or in flat files in a file system, so you may want to come up with some kind of "sharding" strategy, e.g. distribute the data by some hash of the ticker, so you end up with a more manageable number of "tables", say, something in the order of 16 to 1024. The most naive hashing strategy is to split them by the first character of the ticker. Another naive hashing strategy is to split by length of the ticker.
Note: At my day job, we store full order book data for markets with billions of records per day per market. My suggestions are motivated by experience managing petabyte-scale deployments for such data.
