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?

  • 1
    0.10 seconds doesnt seem that much on 500 k rows.
    – Mihai
    Feb 21 '14 at 12:46
  • @Mihai, it sure does add up when the number of rows hit 5M and above. I wonder how Stackoverflow does it in less than 0.1sec with 6.7 million rows. Feb 21 '14 at 13:37
  • ... it's cached? Feb 21 '14 at 13:37
  • @DigitalChris, I don't believe the back pages are cached that often to the extend that they are able to match the total number of questions with the page number x the number of rows. Feb 21 '14 at 13:44
  • StackExchange.init called on every page pulls the sidebar content via js. The entire page doesn't have to be cached monolithically; cached elements can be assembled. Feb 21 '14 at 13:53

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).

  • I got a very high row count (95% of total rows). And from the explain select statement, MySQL still chooses to use the composite index. If I IGNORE INDEX, then the counting takes 8 times longer (0.8sec). Feb 22 '14 at 2:58
  • Looks like you can't do any better than the composite index for the count performance. Feb 22 '14 at 3:02

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.