4 ms·

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.

saurik 15y ago
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.)
- HarrisonFisk 15y ago
  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.
  - saurik 15y ago
    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.
charliesome 15y ago
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
- mzarate06 15y ago
  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)?
  - charliesome 15y ago
    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...
mzarate06 15y ago
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?

saurik 15y ago

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

HarrisonFisk 15y ago
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.
- saurik 15y ago
  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.

charliesome 15y ago

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

mzarate06 15y ago
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)?
- charliesome 15y ago
  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...

mzarate06 15y ago

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?