4 ms·
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 I
by 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 agoThere 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 agoInteresting; 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.