Mysql – Improve reading speed of MySQL table storing stock prices (100 Mil. entries)

MySQLquery-performanceselect

I have a table which stores stock prices in a MySQL database with the following structure:

CREATE TABLE IF NOT EXISTS `tbl_prices` (
  `price_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stock_id` int(11) NOT NULL,
  `datasource_id` int(11) NOT NULL,
  `price_time` bigint(20) NOT NULL,
  `price_time_frame` bigint(20) NOT NULL,
  `price_open` double NOT NULL,
  `price_close` double NOT NULL,
  `price_low` double NOT NULL,
  `price_high` double NOT NULL,
  `price_volume` double NOT NULL,
  PRIMARY KEY (`price_id`),
  UNIQUE KEY `unique_entry` (`stock_id`,`datasource_id`,`price_time`,`price_time_frame`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

The table mainly acts as a storage, meaning that all data for each stock and time frame is loaded into memory on application start. New stock prices are continuously written to the database.

A common query for loading all prices for a given stock looks like this:

SELECT price_open, price_high, price_low, price_close, price_volume, price_time 
FROM tbl_prices
WHERE stock_id=1 AND datasource_id=2 AND price_time_frame=864000
ORDER BY price_time;

EXPLAIN:

EXPLAIN SELECT price_open, price_high, price_low, price_close, price_volume, price_time 
FROM tbl_prices
WHERE stock_id=1 AND datasource_id=2 AND price_time_frame=864000
ORDER BY price_time;

results in:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  tbl_prices  range   unique_entry    unique_entry    24  NULL    113036  Using index condition

The table contains more than 100 million entries.

Everything works fine but loading of the prices from the table uses a considerable amount of time. How could I speed up the table architecture?

Regards,

Best Answer

This does not answer the whole question but as a first hint, I would suggest changing the order of the columns in the definition of the unique key, from:

`unique_entry` (`stock_id`, `datasource_id`, `price_time`, `price_time_frame`)

To:

`unique_entry` (`stock_id`, `datasource_id`, `price_time_frame`, `price_time`)

This puts first all the column that come into play in the where predicates, and then the ordering column, so this gives the database a better chance to take full advantage of the index.

Also: do you really need select *? The more columns you get, the bigger the payload the database has to process (and return). You should enumerate the columns that you really want. Ideally, you would only extract columns that belong to the index: this makes the index covering for the query, and the database might be ble to execute the whole query by looking at the index only.

Related Question