Why is count(*) slow, when explain knows the answer?

This query: select count() from planner_event takes a very long time to run - so long, I gave up and killed it before it finished. However, when I run explain select count() from planner_event, I can see a column in the output with the number of rows (14m).

How come explain can get the number of rows instantly, but count(*) takes a long time to run?

Explain is using previously gathered statistics (used by the query optimizer). Doing a select count(*) reads EVERY data block.

Here's a cheap way to get an estimated row count:

SELECT table_rows
FROM information_schema.tables
WHERE table_name='planner_event';

Even if you did select count(id), it might still take a very long time, unless you have a secondary index on id (also assuming id is a PRIMARY KEY). Because all data (including Row Data) is stored in B-Tree indexes, performing a select count(PK_COLUMN) is still a considerable amount of IO (needs to reads all data pages). If you have a secondary index on the PK field, it will be able to perform less IO to perform a count.

StackExchange Database Administrators Q#184685, answer score: 30