Slow MySQL SELECT on a large table
I have a table to store prices over time ~ 35k pieces every 15 minutes for 2 weeks. It is approximately 35 million rows in the table. I am trying to do the simplest queries:
SELECT buy_price, sell_price, created_at FROM price_archive WHERE item_id = X
The first, undisclosed run of this query takes about 4-7 seconds to return ~ 1300 rows (per item). This looks ridiculously slow for something so trivial to a database, especially given the presence of an index on the column item_id
.
The table contains 35k rows inserted every 15 minutes and a task is performed every day to remove items from <(2 weeks - 1) ago (to stop the table from growing too much). I suspect this piece of the table is quite large, but could this fragmentation do that badly? If so, would you split by created_at
to delete old data?
1306 rows in set (8.32 sec)
mysql> explain select * from price_archives where item_id = 743;
+----+-------------+----------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | price_archives | ref | index_price_archives_on_item_id | index_price_archives_on_item_id | 5 | const | 1305 | Using where |
+----+-------------+----------------+------+---------------------------------+---------------------------------+---------+-------+------+-------------+
source to share
This is your request:
SELECT buy_price, sell_price, created_at
FROM price_archive
WHERE item_id = X;
The best index for the query - is a composite index: price_archive(item_id, buy_price, sell_price, created_at)
. It is a "spanning" index that can satisfy the query. However, it has a disadvantage. This index can slow down the inserts you make to the table. 140k rows every hour is a lot of data, but keeping that index shouldn't be all that bad.
In databases, you are faced with a non-standard task. The problem with your query is that the 1300 or so rows that are returned are on different data pages. Apparently, the table does not fit into memory on your computer, so this results in 1300 accesses to files on disk. This explains why you see a delay time of several seconds.
Another solution is to ensure that the data tables themselves fit into memory. Although the first undisclosed request will take a little time, subsequent requests should be fairly quick.
source to share
You can add indexes to table columns.
If this problem was with a million + records and the time went from 50 seconds to 10 seconds.
SQL Query to update the table:
ALTER TABLE price_archives ADD INDEX (item_id);
ALTER TABLE price_archives ADD INDEX (buy_price);
ALTER TABLE price_archives ADD INDEX (sell_price);
ALTER TABLE price_archives ADD INDEX (created_at);
source to share