0

I need to create a database for data that looks like this:

enter image description here

I am planning to use this table structure:

CREATE TABLE IF NOT EXISTS `AUDCAD` (
  `date` BIGINT NOT NULL, 
  `open` decimal(7,5) DEFAULT NULL,
  `high` decimal(7,5) DEFAULT NULL,
  `low` decimal(7,5) DEFAULT NULL,
  `close` decimal(7,5) DEFAULT NULL,
  `volume` INT DEFAULT NULL,
  PRIMARY KEY(date),
  UNIQUE (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

date column is minute difference timestamps. It will be unique and if same timestamp insert/update came, can be ignored or override with no problem.

This is my first time planning table structure. Does that sound have problem? Do you have a suggestion over this?

  • 1
    Did you consider using datetime(0) for the date field? This might be useful if you want to use date functions when querying the field (and might save 3 bytes per row, if that matters). – mendosi Nov 19 '16 at 11:43
  • 5
    Primary Key already unique, additional index only increase loading and not give any benefits – a_vlad Nov 19 '16 at 12:40
  • @mendosi.. Sadly system need timestamp queries heavily. Still better? – Ünsal Korkmaz Nov 19 '16 at 15:05
  • 1
    See FROM_UNIXTIME() and UNIX_TIMESTAMP() functions. – Rick James Nov 19 '16 at 16:11
  • We can't finish critiquing the schema until we see the main queries that will use it. – Rick James Nov 19 '16 at 16:11
2

Without knowing the query set that's going to hit this structure, I'll give a generalized answer:

I try to use the TIMESTAMP type for this, which works well with time/date functions, and is timezone agnostic (under the hood it is storing a unix timestamp).

By default, this will only be a 4 byte wide key (instead of an 8 byte bigint), so if you index other columns (which always tacks on the primary key at the end...albeit hidden), those structures can stay skinny and quick as well...and also not eat your RAM ;-).

TIMESTAMP by default has a resolution of 1 second, which is overkill for your purposes, but the easy use of date functions on it makes that worth it.

Main downside: TIMESTAMP is based on a 32-bit unix timestamp integer (it stores that way, but displays as a date/time), so it does eventually run out. At that time (decades from now, when RAM is cheap) you can adjust it, and the date/time display/handling nature of the type will allow you to make the change transparently to your software.

If you want to use it as an integer, you can use FROM_UNIXTIME(your_integer_here) to insert, and UNIX_TIMESTAMP(date) in queries that need that. I'm going to guess though that the date/time format is easier to work with for you though.

Also, you do not require a second UNIQUE key, as any PRIMARY key implies this already, and is included for free in the storage/RAM price of your data table (which is sorted by PK as a B-Tree...your table is an index, and can/will be used as such).

  • 2
    One more advantage of using timestamp (or datetime): If the granularity changes later (say to 30 or 15 or 1 sec instead of 1 minute), no change will be needed in the structure of the table. – ypercubeᵀᴹ Nov 19 '16 at 22:15

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.