Can counting rows be made any faster?
I don't think you are going to get anything better than the composite index. However, the key distribution for the composite index could hamper query performance depending on the (hasPhoto,userStatus) combination you choose. Here is how:

Run this Query

SELECT COUNT(*) rowCount,hasPhoto,userStatus FROM Users GROUP BY hasPhoto,userStatus;


This will show you how sparse and how dense each composite index combination is.

Sparse combinations (low rowCount) should use an index scan.

Dense combinations (high rowCount) should use a full table if the Query Optimizer believes it must read too much of the composite index.

You can try this out by running

EXPLAIN SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a';


This will reveal whether the EXPLAIN plan favors an index scan (Extra column Using where; Using index), or a full index (or table) scan (select_type of ALL or SIMPLE).