Hacker News Reader

Comment by onethumb | Hacker News Reader

That's only true if you don't have any WHERE conditions. If you do, and they're indexed (as described by this author), it'll return the count fast.

View on HN

Right... but PostgreSQL does that as well, so it really isn't relevant to a PostgreSQL vs. MySQL comment, which is what I was responding to. ;P PostgreSQL and InnoDB have the exact same constraints and tradeoffs with respect to count() performance, AFAIK.

(That said, an index doesn't make this magically faster... the fast performance that people credit MyISAM with is that asking for the total number of rows in a table was O(1), as its lack of intricate transaction concurrency allowed it to just keep a global counter; one which of course was difficult and costly to repair after an unclean outage. In comparison, that indexes can optimize a count() over a where is obvious and true of any sane database engine, whether it be PostgreSQL or any of the common MySQL backends.)

There is a big difference with regards to PostgreSQL and InnoDB when you have an indexed COUNT(*) clause.

MySQL can do index only queries, whereas PG currently can not (it is part of the next release iirc). This can often result in a fraction of the disk I/O being done for MySQL.

Interesting; I did not realize that InnoDB supported covering indexes. That said, it is still "only" a constant factor: the difference between O(n) (looking at just the index) and O(2n) (scanning the index, but having to also pull that original record, which you now know the exact position of, to see if it is alive for the current transaction).

Thanks for pointing this out, though! I'm going to go look into how InnoDB manages to handle that.

Actually, I'm seeing that MySQL uses any index available to do count([asterisk]), even if there isn't a where clause. For example, I just ran the following queries on a WordPress database:

[1] select count(object_id) from wp_term_relationships force index(PRIMARY);

[2] select count([asterisk]) from wp_term_relationships

[3] select count([asterisk]) from wp_posts

... all of which produce execution plans that show indexes are being used to perform the count() query. All tables are using InnoDB.

Off topic question: how do you escape an asterisk when entering a reply so that it shows w/in the thread vs. italicizing text?

Interestingly, you can make MySQL counts faster with something like:

WHERE id > 0

For some reason counting with that is a lot faster (from my testing) to just a straight count

Are you sure you're clearing the query cache before each test run? In all my test cases, your statement proves false, by a long shot.

Questions:

1) What version of MySQL are you testing on?

2) What storage engine are the relevant tables using?

3) What does the execution plan show for both cases (with where id > 0 and w/out it)?

I tested this a while ago (using sql_no_cache in the query) and the `WHERE id > 0` query was faster. I can't seem to reproduce it though...

Reply on news.ycombinator.com