MySQL slow query with some specific values (stock data)

Question

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.

Perhaps read about late row look ups. Also, a modest benefit can be obtained by storing an integer key instead of the varchar — Strawberry, Sep 13 '18 at 07:18
Also, it seems odd to store open, close, high, low, in a table of minute data — Strawberry, Sep 13 '18 at 07:24
try to index date and code columns, later in future when database is quite loaded might effect the performance. — Rafee, Sep 13 '18 at 08:00

score 2 · Answer 1 · answered Sep 30 '18 at 02:48

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)

And some EXPLAIN output would be useful, but great answer! – samhain1138 Sep 30 '18 at 03:05 — samhain1138, Sep 30 '18 at 03:05

MySQL slow query with some specific values (stock data)

1 Answers1