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 

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)

    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)

1 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.

  • Yes it's 5.7 NDB Cluster, sorry. For theflag index, yes we are making some other tests with composite. Tunning the query a second time take 308 ms. I'm not sure to well understand "the posts may be scattered", but after controls, it seems that the table (a test table) was populated by a script with a random created_at date at each row. So we have unordered created date in column from 2012 to 2016. It could explain what the "scattered" notion, isnt'it ? Buffer pool size is 134217728 and RAM is 16 Go (just a test server).
    – Zoé R.
    Commented Nov 3, 2016 at 7:35
  • After tests and re-populate table as it would be in the real life (ordered by created_at ASC), add an auto-increment 'ID' in more 'POST_ID' that is not sequential, it seems to be better. About 3 sec the first attempt. Still too much but @Rick, thanks for the "scattered" idea.
    – Zoé R.
    Commented Nov 3, 2016 at 13:48
  • 1
    There are so many differences between NDB and non-NDB, that I don't trust anything I have said. I tagged it 'ndbcluster'; maybe an expert from there can help you. But wait... You say ENGINE=InnoDB; how can it be NDB?
    – Rick James
    Commented Nov 3, 2016 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.