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

1 Answer 1

up vote 1 down vote accepted

I would always create two tables and I would also split the prices tables into many tables as they tend to get really big.

I would do something like this:

Tickers/Companies table:

CREATE TABLE `tickers` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ticker` VARCHAR(10) NOT NULL DEFAULT '0',
    `name` VARCHAR(80) NOT NULL DEFAULT '0',
    `sector` VARCHAR(80) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ticker` (`ticker`)
)
ENGINE=MyISAM;

Depending on amount of data you expect and the way you query for data, I would then split prices data into tables based on datetime. Let's assume you get lots of data for each ticker per day and that you would mostly query this daily data. I would then form tables likes this: price_YEAR_MONTH_DAY

Example for two tables:

CREATE TABLE `price_2014_03_23` (
    `ticker_id` INT(10) UNSIGNED NOT NULL,
    `datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `open` DOUBLE NOT NULL,
    `high` DOUBLE NOT NULL,
    `low` DOUBLE NOT NULL,
    `close` DOUBLE NOT NULL,
    PRIMARY KEY (`ticker_id`, `datetime`)
)
ENGINE=MyISAM;

CREATE TABLE `price_2014_03_24` LIKE `price_2014_03_23`

So a single day select would look like this:

select p.* from price_2014_03_23 as p
left join
  tickers as t on t.id = p.ticker_id
where
  t.id = 1 and
  p.datetime = '2014-03-23 09:00:00'

If you'd want data for more days, you can do it like this:

select
  p.*
from
  (select * from `price_2014_03_23` WHERE `ticker_id` = 1 union all 
   select * from `price_2014_03_24` WHERE `ticker_id` = 1) as p

If you do not have that many data in prices tables, you can simply use only one and query that one.

I usualy split data tables based on queries I perform on it. So, I want to minimize the UNION ALL and make sure all indexes are in place. If you can provide some more insights on data being gethered (amount also) and the information you want to get from it, I can provide more specific solutions.

share|improve this answer

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.