Mysql – Optimize finding newest x rows from category

mariadb-10.1MySQLperformancequery-performance

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

Show table info

Best Answer

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

Related Question