0

I've got a v. simple table currently containing millions of data points of price on a given date:

CREATE TABLE data_point (
    id INT PRIMARY KEY,
    symbol_id INT,
    x DATE,
    y DOUBLE
)

This is a timeseries database and for this release I'll keep data with a daily (or less frequent) period but if I need to add a time component, would it be better to change the data_point table and convert x to DATETIME?

If this did happen, initially all of the existing data would remain unchanged, it would not be adjusted - only future data (and not all of it) would be saved with time.

Or would it be better to add a second table with a one-to-one relationship to data_point, which held the time data?

Performance is more critical than space.

This leads on to the associated question about this table. Which is better in terms of performance for huge amounts of data, when all the queries on data_point will be joins from symbol? (Assuming INT for a primary key will get filled up within the app's expected lifetime)

CREATE TABLE symbol (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE data_point (
    id BIGINT PRIMARY KEY,
    symbol_id INT,
    x DATE,
    y DOUBLE,
    CONSTRAINT FOREIGN KEY (symbol_id) 
        REFERENCES symbol(id)
);

or

CREATE TABLE data_point (
    symbol_id INT,
    x DATE,
    y DOUBLE,
    CONSTRAINT PRIMARY KEY (symbol_id, x),
    CONSTRAINT FOREIGN KEY (symbol_id) 
        REFERENCES symbol(id)
);
2

When in doubt, keep it simple - just use DATETIME. I can think of two reasons to flake TIME off into another table, but neither are commonly applicable:

  1. Your table is very large and in use constantly, so you can't afford to block it while changing a data type. I'd still work around this by adding a new DATETIME column, populating it gradually, and then dropping the old DATE field when possible.
  2. It's very important that you keep your table narrow, and you'll need the time values very rarely.

You've stated that performance is more important than space, but I really wonder if six bytes for DATETIME2(0..2) is really going to make much difference over three bytes for DATE. You need to value your own time, and the time of everyone who tries to understand your system in the future.

Your question about the PK really should be asked separately. However, I concur with Rick James that, in this case, you probably don't need a synthetic key (your id field); just index on symbol_id and date. Assuming you're clustering on the PK, consider putting date first, so you can easily query date ranges. If you primarily filter (not just join) by symbol_id, then put it first in the index instead. If you do both, cluster on { date, symbol_id } and index on symbol_id.

  • I guess I could have made it two questions, but I figured the solution with two tables for date and time cols would impact the second question about primary and foreign keys. – Adam May 25 '17 at 9:47
0

In my experience only 1/3 of tables need id INT AUTO_INCREMENT PRIMARY KEY. The other 2/3 have a "natural" PRIMARY KEY, such as your (symbol_id, date).

The oft-quoted argument against natural PKs is their size. But, unless you have more than one secondary key, that argument does not hold water.

I'm not a fan of FOREIGN KEYs -- they are a bunch of overhead for checking. They imply that you can be lazy about writing 'correct code'.

I'm unclear on your questions about "time". In general I recommend using a single column of type DATETIME instead of two columns - DATE and TIME.

What will the queries be? One cannot really judge a schema, and especially its indexes, without knowing what the SELECTs look like.

Also, is the data coming in 'continually'? (As opposed to bulk load nightly.)

  • The queries will be joining on symbol and sometimes also filtering on date. It will be coming in continually. – Adam May 25 '17 at 9:07
  • I like your contentious point about foreign keys and relational integrity = promoting laziness, if I understand you correctly - but don't foreign keys also speed up queries? Or does query performance rely solely on indexing? – Adam May 25 '17 at 9:50
  • Declaring a FOREIGN KEY implicitly creates an INDEX -- this is a performance boost. But you can create that index manually (so I don't count it). FKs slow down insert/delete/update because the check the "constraint" imposed by the FK. – Rick James May 25 '17 at 15:24

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.