Poor usage of timestamp index

Question

with a small table (about 2 M of rows, 5 classic columns, id, description, title, created_at, coordinates (point)) doing a WHERE created_at BETWEEN ... AND ... ORDER BY created_at LIMIT 50 is very very slow (about 15 seconds)

created_at is timestamp with index.

But when I use EXPLAIN, the column filtered shows 10, which is bad. What could be the cause of this 10 ?

Query is simple, table is innodb, no partition

SELECT * FROM posts 
WHERE created_at BETWEEN '2016-10-28 18:25:00' 
                     AND '2016-11-02 18:25:00' 
ORDER BY created_at DESC 
LIMIT 50;

Explain plan

1   SIMPLE  posts   NULL    range   created_at  created_at  5   NULL    5317    10.00   Using index condition; Using where

Mysql 5.7 clustered version

(Added from comments)

CREATE TABLE posts (
    id int(11) unsigned NOT NULL, 
    title varchar(120) NOT NULL DEFAULT '', 
    description text NOT NULL, 
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    status tinyint(4) NOT NULL DEFAULT '1', 
    context tinyint(4) NOT NULL DEFAULT '0', 
    coordinates point DEFAULT NULL, 
    PRIMARY KEY (id), 
    KEY created_at (created_at), 
    KEY status (status), 
    KEY context (context)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

The EXPLAIN is about as good as it will get. "Range" is good for the WHERE. The ORDER BY, though not mentioned, should be handled as well. And it should stop at 50 (again, no clue in EXPLAIN).

10.00 percent of the table is an estimate based on virtually no information; ignore it.

"5.7 clustered" is a contradiction. Do you mean "NDB Cluster"? Or a Galera-based cluster?

Indexing a flag (status) is usually useless.

More on indexing .

Back to the question of 15 seconds. I agree that the time is unreasonably large. Here are some partial explanations...

• Something else was busy in the system.
• The bulky TEXT column was store off-record, necessitating an extra disk hit. Granted, 50 disk hits should take less than a second.
• The Posts may be scattered around; that is, not stored consecutively. Again, maybe 50 disk hits and another fraction of a second.

I am surprised that it took more than one second, even after allowing for uncached blocks.

Run the query a second time (to counteract the caching); how long does it take?

Another thing to check -- how big is innodb_buffer_pool_size and how big is RAM? A tiny VM could be part of the problem. The buffer_pool being so big that swapping occurs could actually explain 15 seconds.