I need to track stock prices for all 8,000+ stocks on the NYSE, AMX, and NASDAQ for at least 48 hours at a time, with stock price quotes for each stock every 2-4 minutes (less than a 5 minute period).
Over the course of the two days, this will accumulate nearly 8M rows of data, which means it requires 20 seconds+ to query them. That's not enough speed. So, in an effort to speed things up, my thoughts are this:
- Keep a separate table for each stock symbol.
- keep no more than 480 prices in each table (older ones will be moved to symbol_history for long term history)
My reasoning is this: Each table in a database is effectively kept as it's own file in /var/lib/mysql. So, it makes sense that each table would be its own file because when I want to query information about GOOG, MySQL can go straight to the GOOG table without having to sift and sort through 8M rows looking for WHERE symbol = "GOOG".
Because each symbol would be its own table, we can also use SORT BY and SUM() and other functions with ease and speed since the max number of prices in there would be 400 or so. (20 udates / hour, 10 hours / day, for two days).
But wait... that's only 8,000 tables? Where are the other 8,000?
I'll be dumping "historical data" (data that is more than 48 hours old) into another table ([symbol]_history for that stock symbol. That way, if we need it, we can still get access to older data (potentially year's worth), but the data we're currently working with (the most recent 48 hours) will never be more than 400 or so rows.
Does anyone have a better idea?