I need to calculate the total number of rows based on a set of criteria:
SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a';
The above query takes 0.10 sec to run on a MyISAM table with 500,000 rows despite having a composite index on columns userStatus and hasPhoto.
Is there any better way to improve counting performance short of writing the total count in a separate table?
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).
IGNORE INDEX, then the counting takes 8 times longer (0.8sec).
– Question Overflow
Feb 22 '14 at 2:58
The only improvement i could see is in careful selection of your composite index.
Putting the column that will generate more false results as first index should improve performance slightly.
Using a favorable sort order should also have an effect - for example if you used (hasPhoto=1) significantly more often than (hasPhoto=0) you should use a DESC index for that column.
