The query for this problem is fairly simple and has been answered many times before, I however have problems with optimizing it.
Say I have a table with the columns timestamp
(bigint), category
(int), and value
(int). I need to efficiently find the top x rows sorted by timestamp
descending where category matches.
I currently do this via the following query:
SELECT *
FROM table
WHERE category=315
AND timestamp > 1488983069581
ORDER BY timestamp DESC
LIMIT 10
To speed up the query I use an index:
ADD INDEX `fetch` (`category` ASC, `timestamp` ASC);
This speeds up my query significantly.
Sadly this is still pretty slow. My table has a few million entries containing about 300 different categories and a lot of entries per category. ~100 fetches take about a second on my above average home desktop. It is of course a fairly heavy operation on some millions of rows, but I would not expect it to take over a second. It is not the overhead of the requests as a smaller table returns instantly.
I wonder if I just underestimate the time to fetch or that I do something wrong. From a computer science standpoint this is a very easy query where a lot of preprocessing can be applied (on insertion). For example, using a hash table for the category leading into a descending sorted list would give pretty much constant search time. My MySQL knowledge is limited though, advice would be very much appreciated.
Version: 10.1.16-MariaDB (32-bit)
I would say I have about 1GB RAM available for MySQL. If 1GB is very little, I could probably add more, up to 4GB. The thing is I would like to save some for the application that uses the queries.
innodb_buffer_pool_size
16MB.
Create table
delimiter $$
CREATE TABLE `data_f32` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category` int(10) unsigned NOT NULL,
`timestamp` bigint(20) unsigned NOT NULL,
`value` float DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `data_f32_foreign_data_id_idx` (`category`),
KEY `fetch` (`category`,`timestamp`) USING BTREE,
CONSTRAINT `data_f32_foreign_category` FOREIGN KEY (`category`) REFERENCES `config_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13648145 DEFAULT CHARSET=latin1$$
Execution plan
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | data_f32 | range | "data_f32_foreign_category_idx,fetch" | fetch | 12 | NULL | 94642 | "Using where"
DB info