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
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.
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$$
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"