0

I want to create a relational database for financial data and struggling to figure out what the best approach to this is.

  • 1st option: single table for all records with an index
  • 2nd option: one table for each trading pair, but this feels incorrect because conceptually all records are the same (they represent candles)

I am wondering what the performance differences between the two would look like. My understanding is using a single table with an index should have the same performance than using multiple tables, because under the hood the database should create a tree that lets me quickly find data relative to a given pair. So the question is: how is an index (I believe b-tree should be suited to this case) handled by the database at the low-level? Does it provide the same performance than separating data in multiple tables?

Now assuming the single-table approach and this schema

CREATE TABLE Candle (
  Pair VARCHAR(127) NOT NULL,
  OpenTime TIMESTAMP NOT NULL,
  Open DECIMAL(20, 8) NOT NULL,
  High DECIMAL(20, 8) NOT NULL,
  Low DECIMAL(20, 8) NOT NULL,
  Close DECIMAL(20, 8) NOT NULL,
  BaseVolume DECIMAL(20, 8) NOT NULL,
  QuoteVolume DECIMAL(20, 8) NOT NULL,
  PRIMARY KEY (Pair, OpenTime),
  INDEX(Pair, OpenTime)
);

The cardinality of Pair is limited (a few hundreds values) whereas OpenTime has hundreds of thousands of possible values (I want to store 1m candle data, so years of data would result in a lot of different values). Does the index on (Pair, OpenTime) make sense or it's better to have an index on Pair only? The idea between using both is I believe it should allow for faster queries that include sorting or filtering by timestamp. However, it should also increase the depth of my tree, although I don't know if the increase would be enough to cause noticeably slower queries. Please note that there are going to be billions of records in total.

Thanks in advance and let me know if I can improve my question in any way!

5
  • Tables don't have performance. They just sit there, storing data. Queries have performance. No one can answer this question without knowing which specific query or queries you want to optimize. Depending on the query, the answer to your question might be one table with indexes, or multiple tables, or even some different answer. Feb 9 at 15:44
  • Not related directly to your question but I like to follow the Swart’s Ten Percent Rule even if it is for SQL Server it applies to MySQL as well. So do not keep same repeated data on different tables , keep the tables as small as possible. This was an idea how to store data. As per performance as Bill mentioned above it depends on the type of queries. By the way INDEX(Pair, OpenTime) is excess as you have PRIMARY KEY (Pair, OpenTime). Feb 9 at 15:54
  • @BillKarwin Sorry, I thought the question was self explanatory, I'm interested in SELECT queries to retrieve data about a given pair while filtering by timestamp
    – DamiToma
    Feb 9 at 18:12
  • I have worked with such a dataset before, but it involved a "ticker" or "symbol"; what is a "pair"??
    – Rick James
    Feb 9 at 18:17
  • @RickJames Kind of synonym for ticker or symbol. Not exactly the same but in this context it's just fine.
    – DamiToma
    Feb 9 at 19:04

1 Answer 1

0

Single table, PRIMARY KEY(symbol, timestamp). That serves as a unique index, so there is no need to have a redundant INDEX(symbol, timestamp).

Please include the main queries you intend to apply to the table. I can probably provide more tips.

The size of the table schema depends on the data types, timespan, and indexes. DECIMAL(20, 8) takes 10 bytes, is it overkill? Open-high-low-close probably need less. Maybe (6,6) (6 bytes) Volume may need that that much range, but maybe not that much precision. Maybe: (11,0) (5 bytes)

pair should probably be normalized and replaced by a 3-byte MEDIUMINT.

If you have the raw data, and data for candles, that could be two separate tables -- the raw data as discussed above, and a summary table (by day?) that uses FLOAT (4 bytes -- sufficient range and precision for graphing).

Not a separate table for each pair.

Re Indexing in InnoDB

The PRIMARY KEY, together with the rest of the columns is stored in a "B+Tree". Hence it is ordered by the PK.

Each secondary key contains the columns of the key, plus the columns of the PK. It is also stored and ordered as a B+Tree. When looking up the row(s) via a secondary INDEX, the PK is found in the B+Tree. If more columns are needed, then it must do a second lookup in the data's B+Tree.

When you have a "composite index" (i.e., multiple columns), think of those columns as being concatenated together into a single string that serves for lookups and ordering of the table or index.

At a low level, it is messier because of the different data types and encoding, but that should not matter to the user. All datatypes are "well ordered" -- two values, a and b are either a<b, a=b, or `a>b'.

  • INT needs to worry about SIGNED vs UNSIGNED
  • The various date and timestamp and ENUM datatypes are stored as unsigned ints of suitable length
  • VARCHAR has a 1- or 2-byte length field plus characters in the specified character set. The COLLATION determines whether to differentiate between upper and lower case, plus accents (quite complex).
  • DECIMAL packs 2 decimal digits in one byte or 9 digits in 4 bytes.
  • FLOAT and DOUBLE will, on most computers, use the IEEE-754 encoding and rules.

Multiple Tables

It is almost never advisable to split a large table into multiple tables on some column, no matter how large. Opening another table is more costly (speed and space) than reaching into a BTree. This admonition includes the use of PARTITION, though this mechanism has a very few other advantages.

3
  • I was more interested in understanding how an index is implemented under the hood. DECIMAL(20, 8) is definitely an overkill for most applications and agreed on the INDEX, sorry about the mistake. The query I'm curious about are normal SELECT queries that fetch a list of candles for a given symbol. Thanks for the help!
    – DamiToma
    Feb 9 at 19:05
  • I added details on indexing. Still more questions?
    – Rick James
    Feb 9 at 19:45
  • That's exactly what I was looking for. Thanks!
    – DamiToma
    Feb 10 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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