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)
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;
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;