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

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,

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.

as sysadmin I would suggest:

• Use index for stock_id,datasource_id,price_time_frame (where or order by column)
• Use Solid-state drives over HDDs ( a single 7200 RPM drive is limited to 200 I/O and an enterprise SSD disk is 50,000 I/O)
• Edit Mysql Server Settings like:innodb_buffer_pool_size,innodb_io_capacity
  this combination between table schema , server settings and hardware will give you very good preformence

Do you need price_id for anything? If not, get rid of it and promote the UNIQUE KEY to be PRIMARY KEY.

InnoDB “clusters” the PK with the data. Hence, when you fetch rows based on the PK (as GMB suggested), the rest of the data will be right there. That is, there is no need to reach over into another BTree to get the other columns.

This might more than double the speed (for your case).