Performance difference between multiple tables and indexed single table

Question

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!

Answer 1

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.