7

I am downloading a bulk of stock data:

Ticker symbol, company name, industry sector,

Open, high, low, close

I was wondering performance-wise if separating the ticker, company name, industry sector from the price data would be optimal, as the tickersymbol and companyname just repeat over and over again.

Or would inner joining the ticker+price every time be slower?

So:

  • single table (ticker/price), or
  • two tables (ticker)(price)?
  • 1
    Probably suitable for Database Administrators – hjpotter92 Mar 23 '14 at 23:31
  • 1
    I actually think this a legit question for SO, as it involves database design, which is a common topic here. – Phil Sandler Mar 24 '14 at 0:47
  • What's the source? – Munchi Mar 24 '14 at 9:21
3

Storing the data in one table is not necessarily faster. If you move company name and industry out of the table, each row will be smaller. Smaller rows means more rows per data page. And that means fewer data pages.

You can have a situation where the rows with the company name would be larger than memory. But the pages without the company name would fit in memory. Clearly, the latter situation will result in faster queries.

However, to even begin answering questions about performance, one needs information about the types of queries being run. This will inform not only the table structure, but also the indexes and data types that might be necessary.

2

If speed is your one and only concern, and the data is for reading only (not updating/creating): all other things being equal, storing all the data in one table will be faster.

Storing it in separate tables is storing it "normalized" (Normalization), and storing it in one table is storing it "denormalized" (Denormalization),

2

Back of the envelope calculation:

  • MySQL Ints take 4 byes

  • A stock ticker symbol will be 3-5 bytes.(I'm guessing?) If you do "CHAR(5)" then every symbol will take 5 bytes.

So with 4 vs 5 bytes, you won't see a whole lot of difference. But if SOME your stocks are longer, you'll want to use VARCHAR, and it will be slightly less performant.

If you go with 2 tables, inserting will be slower because it must do lookups in one table before inserting in the other table. The joins will be a tiny bit slower.

But if you care about performance more than anything else, you could always cache the entire stock-to-ID mapping table on your client. Then queries won't involve joins at all.

To really answer your question, you'd need to know exactly what queries you need and how frequent they are, then test it in various scenarios.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.