Xrhrft

I have a database with nearly 100M rows of stock data. Pulling the data for the last 7 days to generate a chart takes a couple seconds.

My thought is to create partitions that represent the normal chart periods (1 day, 1 week, 1 month, 6 months, 1 year, for example).

How does MySQL handle new data for these partitions? I assume that they would be INSERTed into the 1 day partition, because they are "today's" numbers. But what happens tomorrow? Are they automatically moved to the 1 week partition because they no longer belong to "today"?

From reading the docs, seems partitions is most appropriate for fixed, "in the past" data with fixed ranges that can be identified by the key (year, for example...) not the result of a function that gives a dynamic value (like distance into the past in days).

Edit to include requested information

Will your data be bigger than what can be cached in RAM?: Yes

There are actually two tables: a symbol_history and a symbol_current keeps 48 hours of intra day stats available for calculations in a proprietary algorithm.

At night, after the market closes, data older than 48 hours is computed for open, high, low, close, and we just save that information on a per stock basis. For our purposes, OHLC is the only thing required once data is 2 days old.

I have added a SCHEDULED EVENT that deletes information from current_symbol if it is older than that. This dumped 77M rows of data that was quite stale (not sure why this wasn't done before, but I digress).

Now, we still have 83M rows of data in symbol_history, which is used to generate candle sticks for historical perspective.

Generating charts from symbol_current is now blazing fast. But incorporating symbol_history has the same slow speed problem as before.

Any thoughts are appreciated.

CREATE TABLE `symbol_current` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `stocks_id` int(11) NOT NULL,

  `last_trade` double DEFAULT NULL,

  `open` double DEFAULT NULL,

  `ask` double DEFAULT NULL COMMENT 'The c urrent ask price',

  `bid` double DEFAULT NULL COMMENT 'The c urrent bid price',

  `change_close` double DEFAULT NULL COMMENT 'The dollar change of the las t price from the previous close',

  `change_close_percentage` double DEFAULT NULL COMMENT 'The percentage change of the last price f rom the previous close',

  `high` double DEFAULT NULL COMMENT 'The highest price at  which a security ha s traded during the current day',

  `low` double DEFAULT NULL COMMENT 'The lowest price at which a security has traded during the current day',

  `total_volume` bigint(20) DEFAULT NULL COMMENT 'Total number of share s or contracts  exchanging hands',

  `ohcl_calculation` tinyint(1) DEFAULT NULL,

  `updated_at` timestamp NULL DEFAULT NULL,

  `record_creation_time` timestamp NULL DEFAULT NULL,

  `created_at` timestamp NULL DEFAULT NULL,

  `python_time` text,

  `delete_after` datetime DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `new_fk_symbol_current_stocks1_idx` (`stocks_id`),

  KEY `new_ohcl_calculation` (`ohcl_calculation`),

  KEY `new_symbol_current_create_at` (`created_at`),

  KEY `last_trade` (`last_trade`),

  KEY `idxDeleteAfter` (`delete_after`),

  CONSTRAINT `new_fk_symbol_current_stocks1` FOREIGN KEY (`stocks_id`) REFERENCES `stocks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=634299 DEFAULT CHARSET=utf8;

Here's symbol_history:

CREATE TABLE `symbol_history` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `stocks_id` int(11) NOT NULL,

  `open_price` double DEFAULT NULL,

  `close_price` double DEFAULT NULL,

  `high_price` double DEFAULT NULL,

  `low_price` double DEFAULT NULL,

  `total_volume` bigint(20) DEFAULT NULL COMMENT 'Total number of share s or contracts  exchanging hands',

  `updated_at` timestamp NULL DEFAULT NULL,

  `created_at` timestamp NULL DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_symbol_current_stocks1_idx` (`stocks_id`),

  KEY `symbol_history_created_at` (`created_at`),

  CONSTRAINT `fk_symbol_history_stocks1` FOREIGN KEY (`stocks_id`) REFERENCES `stocks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=98852145 DEFAULT CHARSET=utf8;

This use of PARTITIONing is unlikely to achieve much. More in a minute...

When a row is INSERTed into a PARTITIONed table, it looks at the partition definition to decide which partition to put the row in. Then it inserts the row into that one partition. A partition is essentially a table. (I like to call it a sub-table.) Each sub-table has its own index(es) referring only to the rows in that sub-table.

"Today" and "tomorrow" do not matter unless you, for example, PARTITION BY RANGE (TO_DAYS(datetime)). Then, depending on the specific partitions, today and tomorrow might be separate partitions.

There is no "automatic" way to partition "by week". You have to do it manually (or with some automated cron task). See http://mysql.rjweb.org/doc.php/partitionmaint for a discussion of this particular partitioning task.

You are unclear on the charts you want. For a 1-month chart, do you only want the 30 days ending now? Or any full month (1st thru 31st)? Or any arbitrary N days? The makes a big difference in optimization.

Next, when making a 1-day chart, do you want data points for each trade? For a 1-year chart, do you want candlesticks or spark lines?

And, for the 1-day chart, do you want the detail, even for one day last year? Or can the details be tossed. That is, new data has all trades; old data has only open-high-low-close for each day (or maybe only week/month).

Some of that is leading to suggestions of "summary tables"; see http://mysql.rjweb.org/doc.php/summarytables

Back to partitioning... Partitioning won't help with speed. It will help if you need to delete old data. (See Case 1 in my partitioning blog.)

Another issue... Indexing. Will your data be bigger than can be cached in RAM? If so, we need to talk about the PRIMARY KEY and perhaps one secondary index, so as to optimize fetching of data for charting, possibly at the expense of INSERTing.

Please provide SHOW CREATE TABLE, how many tickers you will store, how much granularity of transactions, how much RAM, etc.

Critique of schema...

Size leads to cacheability, which leads to speed. So, much of the following critiques the speed.

With rare exceptions (BRK), the ~7 significant digits of FLOAT is sufficient for prices. FLOAT takes 4 bytes; DOUBLE takes 8. For volume, I suggest DECIMAL(11,0), which takes 5 bytes (versus 8 for BIGINT. I have not seen more than 100 billion shares traded at once, have you? (I have seen more than 2^32 in a single day, so I don't recommend INT UNSIGNED.)

I doubt if you have more than 16M stocks, so change stocks_id from INT (4 bytes) to MEDIUMINT UNSIGNED (3 bytes).

I see no DATE in the history, unless it is python_time. Consider using a 3-byte DATE. Then get rid of id and replace the PK by PRIMARY KEY(stocks_id, date). This change will significantly speed up any chart for a single stock over any date range. Meanwhile, get rid of the now redundant new_fk_symbol_current_stocks1_idx.

DATETIME is 5 bytes; consider using DATE for delete_after.

Rethink the rest of the indexes. They cost something during INSERT, and they cost disk space.

How big is a python time? If you really need it, can you make it a smallish VARCHAR, not TEXT? There may be issues with TEXT. Please provide the actual SELECT used against History.