count - 如何加速 Interbase/Firebird 中的 Count(*)

Question

Interbase 是一个分代数据库。

这很好，因为回滚几乎是瞬时的，但是 count(*)需要永远。
这是不像 例如MySQL where count 可以使用索引。

我一直不知道为什么，直到我看到这个:

Even when an index is available on the column or columns included in the COUNT, all records must be visited in order to see if they are visible under the current transaction isolation.

在维基百科上: http://en.wikipedia.org/wiki/InterBase

关于如何在 Interbase/Firebird 中进行快速计数的任何提示

Answer 1

根据此链接:http://www.firebirdfaq.org/faq5/

There is another solution. This one is by Ivan Prenosil, a long time Interbase and Firebird hacker. This solution only returns an approximate record count. As Ann W. Harrison kindly explains: Any record that has had its primary key modified will appear twice if the old version has not been garbage collected and deleted records will continue in the count until they are garbage collected.

/* first update the statistics */
UPDATE RDB$INDICES SET RDB$STATISTICS = -1;
COMMIT;

/* Display table names and record counts */
SELECT RDB$RELATIONS.RDB$RELATION_NAME,
CASE 
WHEN RDB$INDICES.RDB$STATISTICS = 0 THEN 0 
ELSE CAST(1 / RDB$INDICES.RDB$STATISTICS AS INTEGER) 
END 
FROM RDB$RELATIONS 
LEFT JOIN RDB$RELATION_CONSTRAINTS 
 ON RDB$RELATIONS.RDB$RELATION_NAME = RDB$RELATION_CONSTRAINTS.RDB$RELATION_NAME 
 AND RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN RDB$INDICES 
  ON RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME = RDB$INDICES.RDB$INDEX_NAME 
WHERE RDB$VIEW_BLR IS NULL AND RDB$RELATION_ID >= 128 
ORDER BY 1;

This will only work on tables that have a primary key.