CODEWITHSUNDEEP
mysqldatabaseperformancestock-data
r0beRT
r0beRT

Reputation: 21

MySQL slow query with some specific values (stock data)

I have some stocks data like this

+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| date   | datetime      | YES  | MUL | NULL    |       |
| open   | decimal(20,4) | YES  |     | NULL    |       |
| close  | decimal(20,4) | YES  |     | NULL    |       |
| high   | decimal(20,4) | YES  |     | NULL    |       |
| low    | decimal(20,4) | YES  |     | NULL    |       |
| volume | decimal(20,4) | YES  |     | NULL    |       |
| code   | varchar(6)    | YES  | MUL | NULL    |       |
+--------+---------------+------+-----+---------+-------+

with three indexes, a multi-columns index of date and code, an index of date and an index of code.

The table is large, with 3000+ distinct stocks and each stock has minute data of nearly ten years.

I would like to fetch the last date of a specific stock, so I run the following sql:

SELECT date FROM tablename WHERE code = '000001' ORDER BY date DESC LIMIT 1;

However, this query works well for most stocks (<1 sec) but has very bad performance for some specific stocks (>1 hour). For example, just change the query to

SELECT date FROM tablename WHERE code = '000029' ORDER BY date DESC LIMIT 1;

and it just seems to freeze forever.

One thing I know is that the stock "000029" has no more data after 2016 and "good" stocks all have data until yesterday, but I'm not sure if all "bad" stocks have this characteristic.

Upvotes: 2

Views: 232

Answers (1)

Rick James
Rick James

Reputation: 142518

First, let's shrink the table size. This will help speed some.

  • decimal(20,4) takes 10 bytes. It has 16 decimal places to the left of the decimal point; what stock is that large? I don't know of one needing more than 6. On the other hand, is 4 on the right enough?
  • Normalize the 'code'. "3000+ distinct stocks" can be represented by a 2-byte SMALLINT UNSIGNED NOT NULL, instead of the current ~7 bytes.
  • '000029' smacks of ZEROFILL??
  • DESCRIBE is not as descriptive as SHOW CREATE TABLE. What is the PRIMARY KEY? It can make a big difference in this kind of table.
  • Do not make any columns NULL; make them all NOT NULL.
  • Use InnoDB and do have an explicit PRIMARY KEY.

I would expect these to be optimal, but I need to see some more typical queries in order to be sure.

PRIMARY KEY(code, date)
INDEX(date)

Upvotes: 2

Related Questions