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;
OPTIMIZE TABLE
.