Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?
share|improve this question

migrated from stackoverflow.com Mar 24 at 2:20

This question came from our site for professional and enthusiast programmers.

1  
Probably suitable for Database Administrators –  hjpotter92 Mar 23 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 at 0:47
    
What's the source? –  Munchi Mar 24 at 9:21
add comment

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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),

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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