Optimize finding newest x rows from category

Question

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

Answer 1

_{Community Wiki answer compiled from comments on the question}

---

You should probably check the InnoDB settings. You may have given very little RAM for MySQL to use. How much RAM do you have, and what's the setting of innodb_buffer_pool_size?

It's usually set up around 70-80% of RAM in servers. Or more if your RAM is larger (say, more than 64GB).

Your index is good, yes. You might get better performance if you make that index (data_id, timestamp) the PRIMARY KEY (and demote the (id) to a simple index). That would probably improve performance of the query (to a few milliseconds) at the expense however of slightly slower writes.

The UNIQUE (id) index is redundant. You can remove it (but that is irrelevant to the issue). I would probably remove the (data_id) index as well. The fetch index covers that as well. - ypercubeᵀᴹ

---

A tiny (16M) buffer pool cache leads to a lot of I/O. - Rick James