Implement database schema for organizing historical stock data

Question

I am new to MySQL, I try to use MySQL to store my stock data.

I followed the answer by boe100 in reference:

• Database schema for organizing historical stock data

I create my table as follows:

mysql> describe StockDailyQuotations;

Field	Type	Null	Key	Default	Extra
ts_code	varchar(9)	NO	PRI	NULL
trade_date	int(8)	NO	PRI	NULL
open	decimal(6,2)	NO		NULL
high	decimal(6,2)	NO		NULL
low	decimal(6,2)	NO		NULL
close	decimal(6,2)	NO		NULL
change	decimal(6,2)	YES		NULL
pct_chg	float	YES		NULL
vol	float	YES		NULL
amount	float	YES		NULL

10 rows in set (0.00 sec)

I always use the table in two following ways:

(1) search one stock's history data, It takes 0.01 seconds.

SELECT * FROM StockDailyQuotations WHERE ts_code='000001.SZ';

(2) search all stock's data in one day. It takes 1.94 seconds.

SELECT * FROM StockDailyQuotations WHERE trade_date='20201231';

The answer in reference said: "We also have a clustered index on symbol, date and time columns. We can get data out of the server in a matter of milliseconds. Remember, the database size is almost 1 terabyte." But in my case, searching 1 is fast enough, I want to accelerate type 2 searching.

I think the primary key on ts_code and trade_date is already made the clustered index. Do I misunderstand anything? How can I accelerate the searching (2)?

I apologize if it is a stupid problem. Thanks for your time.

Answer 1

I create an index for 'trade_date', searching way (2) can have same performance with way (1).

Answer 2

(It's not a "stupid problem", just a "novice question".)

PRIMARY KEY(ts_code, trade_date)
INDEX(trade_date)

But have trade_date DATE (not INT)

DECIMAL(6,2) limits you to 9999.99; is that OK?

Use ENGINE=InnoDB

Be cautious of other Questions that are not tagged [mysql] or [mariadb]; they are likely to have syntax and other suggestions that are not good for MySQL.

If you include "time", it is probably better to use a single DATETIME column, not two columns (DATE and TIME). However, this leads to some tricky business when requesting info for a given date.