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:

  1. Keep a separate table for each stock symbol.
  2. 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?

  • 3
    How do you come up with 8M rows taking 20+ seconds to query? – Max Vernon Dec 9 '15 at 22:43
  • 4
    "Does anyone have a better idea?" Yes, index your table. 8M rows is a small table. We don't know what queries you are doing but unless they are very complicated, they should not take 20 seconds. – ypercubeᵀᴹ Dec 9 '15 at 22:47
  • In InnoDB the Primary key is a clustering key - so making the stock symbol (or better its ID referencing a table with some stock details) first part in a primary key and it is almost the same as having multiple tables in the way of organization of data on disk, but much simpler to do. 8k inserts in 2 minutes should be quite doable for decent HW - but you can simply test that to be sure. – jkavalik Dec 10 '15 at 7:45
  • Just to reinforce what others have said, having 16000 tables is Terribad – Philᵀᴹ Dec 10 '15 at 12:41
  • 1
    Show us the SELECTs. From them, we can discuss the number of indexes, partitioning, and other options. (No to 16K tables!) – Rick James Dec 11 '15 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.