So this is about a SELECT COUNT(*) FROM tblname
without a WHERE clause. MyISAM has an
optimisation for that since it maintains a rowcount for each
table. InnoDB and PBXT can’t do that (at least not easily)
because of their multi-versioned nature… different transactions
may see a different number of rows for the table table!
So, it’s kinda known but nevertheless often ignored that this
operation on InnoDB is costly in terms of time; what InnoDB has
to do to figure out the exact number of rows is scan the primary
key and just tally. Of course it’s faster if it doesn’t have to
read a lot of the blocks from disk (i.e. smaller dataset or a
large enough buffer pool).
I was curious about PBXT’s performance on this, and behold it
appears to be quite a bit faster! For a table with 50 million
rows, PBXT took about 20 minutes whereas the same table in InnoDB
took 30 minutes. Interesting! …[Read more]