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;
Best Answer
The
EXPLAIN
is about as good as it will get. "Range" is good for theWHERE
. TheORDER BY
, though not mentioned, should be handled as well. And it should stop at 50 (again, no clue inEXPLAIN
).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...
TEXT
column was store off-record, necessitating an extra disk hit. Granted, 50 disk hits should take less than 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.