3 ms·

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

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.