I am new to MySQL, I try to use MySQL to store my stock data.
I followed the answer by boe100 in reference:
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.
Advertisement
Answer
(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.