Mysql WHERE ... ORDER BY taking too long in large database

Asked Mar 12 '22 at 14:55

Active Mar 12 '22 at 15:54

Viewed 60 times

I have a large database. There are 22 million lines for now, but there will be billions of lines in the future. I have attached example lines where timestamp is in milliseconds. What I want to do is get the last line every second.

SELECT *
FROM   btcusdt
WHERE  1502942432000 <= timestamp
       AND timestamp < 1502942433000
ORDER  BY tradeid DESC
LIMIT  1

The query I wrote above works fine, but the WHERE condition takes too long because it scans all the rows in the table, but actually It doesn't need to scan all the lines because the time is already sequential. As soon as it doesn’t fit the where condition, it should finish scanning. Any suggestions on how I can speed this up?

db example

edited Mar 12 '22 at 15:54

RF1991

2,037
4
8
17

asked Mar 12 '22 at 14:55

Tufan YAVAŞ

4

One word: indexes ;-) – Álvaro González Mar 12 '22 at 14:56
1

I think I need more words :D – Tufan YAVAŞ Mar 12 '22 at 15:02
2

for such questions we need to see CREATE TABLE and EXPLAIN ANALYSE SELECT query – nbk Mar 12 '22 at 15:05
also mysql has a very good manual about that https://dev.mysql.com/doc/refman/8.0/en/select-optimization.html – nbk Mar 12 '22 at 15:09
1

Thanks Álvaro i add index to timestamp and its fixed – Tufan YAVAŞ Mar 12 '22 at 15:15

Mysql WHERE ... ORDER BY taking too long in large database

0 Answers0