Should I avoid COUNT all together in InnoDB?

Question

Right now, I'm debating whether or not to use COUNT(id) or "count" columns. I heard that InnoDB COUNT is very slow without a WHERE clause because it needs to lock the table and do a full index scan. Is that the same behavior when using a WHERE clause?

For example, if I have a table with 1 million records. Doing a COUNT without a WHERE clause will require looking up 1 million records using an index. Will the query become significantly faster if adding a WHERE clause decreases the number of rows that match the criteria from 1 million to 500,000?

Consider the "Badges" page on SO, would adding a column in the badges table called count and incrementing it whenever a user earned that particular badge be faster than doing a SELECT COUNT(id) FROM user_badges WHERE user_id = 111?

Using MyIASM is not an option because I need the features of InnoDB to maintain data integrity.

Answer 1

SELECT COUNT(*) FROM tablename seems to do a full table scan.

SELECT COUNT(*) FROM tablename USE INDEX (colname) seems to be quite fast if the index available is NOT NULL, UNIQUE, and fixed-length. A non-UNIQUE index doesn't help much, if at all. Variable length indices (VARCHAR) seem to be slower, but that may just be because the index is physically larger. Integer UNIQUE NOT NULL indices can be counted quickly. Which makes sense.

MySQL really should perform this optimization automatically.

Answer 2

Performance of COUNT() is fine as long as you have an index that's used.

If you have a million records and the column in question is NON NULL then a COUNT() will be a million quite easily. If NULL values are allowed, those aren't indexed so the number of records is easily obtained by looking at the index size.

If you're not specifying a WHERE clause, then the worst case is the primary key index will be used.

If you specify a WHERE clause, just make sure the column(s) are indexed.