2011/9/26 Platonides <Platonides(a)gmail.com>
Roan Kattouw wrote:
As you say, there are gaps in the rev_id sequence
due to undeleted
revisions and possibly the occasional transaction-rollback-induced
gap. The real real number would be SELECT COUNT(*) FROM revision; but
that'll probably take like half an hour to count all rows.
Wouldn't it be able to use the index in rev_id to return it in O(1) ?
It takes ages in the toolserver view, but I guess it would be the
428660376 rows it shows in the explain.
Index row counts are approximate (and sometimes *wildly* inaccurate) in
InnoDB -- the only way to get an accurate count is to actually go through
the whole table (or at least the whole index) and count the entries, which
takes time and memory and holds a transaction open while it works.
You can get the top or the bottom entry in the index very quickly -- but
there simply isn't an accurate count ready to read.
(MyISAM tables do store actual counts in the indexes which COUNT(*) can
optimize on, but that basically never helps you in practice since we don't
like our databases to crash. ;)
-- brion