Is MySQL's COUNT() function an estimate?

Question

I performed the following query on a MyISAM table three times in a row:

mysql> SELECT COUNT(*) FROM tickets WHERE created_time BETWEEN UNIX_TIMESTAMP() - 86400 * 20 AND UNIX_TIMESTAMP() - 86400 * 19;

The tickets table only receives inserts where the created_time column is set to UNIX_TIMESTAMP(), rows are never deleted, and the created_time of a row is never updated. Despite this, the results I got were 154324, 154326, and 154325 (in that order). This leads to me to believe the COUNT() function doesn't return an exact number, but my brief search through MySQL's documentation and the rest of the web didn't yield any mention of this. Does anyone know what COUNT() does, exactly?

Answer 1

What's wrong with the results you are getting?

The time window is shifting as you run the query - first two new results come into the window and then one of the old results gets out (too old).

The COUNT is implemented differently for different scenarios. If you don't limit the query using WHERE and the query is executed on single table then some stored internal counter is used to generate the result. If you limit the query then the results are first filtered, using the table data or indexes, and the resulting rows are then counted.

Answer 2

There are several issues with the count(*) on myIsam tables, since MyIsam uses indices (as opposed to innodb that makes a table scan) for the count, there may be some problems with your index....

What version of mysql are you using?

Answer 3

It counts the number of rows. Exactly. Each time you ran it, UNIX_TIMESTAMP was different, therefore making your where clause different.

This is entirely expected behavior.