Re: COUNT(*) on innodb

[James Day]

One option you could try is to have an index on a small field or small part of a field and try counting with a use or force index option specifying that index. It would still be a full scan but of an amount of data which may more easily fit in RAM.

If you have an autoincrement field and deletes are uncommon, you could compare the minumum and maximum values of the indexed autoincrement column to get a reasonable count with only two index checks. If you need more accuracy, you could periodically store the counter and accurate record count and subtract that value from the current value.

If you know the timestamp of a saved count and have an index on a timestamp column you could relatively quickly count only the records with a timestamp greater than that using the index.

James Day
Support engineer, MySQL AB