1

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?

7
  • 1
    0.10 seconds doesnt seem that much on 500 k rows.
    – Mihai
    Commented Feb 21, 2014 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. Commented Feb 21, 2014 at 13:37
  • ... it's cached? Commented Feb 21, 2014 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. Commented Feb 21, 2014 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. Commented Feb 21, 2014 at 13:53

2 Answers 2

2

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

2
  • 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). Commented Feb 22, 2014 at 2:58
  • Looks like you can't do any better than the composite index for the count performance. Commented Feb 22, 2014 at 3:02
0

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.

2
  • 1
    The column order should not matter if the whole index is being utilized. MySQL has yet to implement DESC index. Commented Feb 21, 2014 at 13:48
  • Your answer that column order doesn't matter surprised me a bit, so I looked further. stackoverflow.com/questions/9537128/… suggests otherwise. Commented Feb 21, 2014 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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