2011/9/26 Platonides Platonides@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