在SQLite中count（*）是固定时间的吗？如果不是，那有什么替代方法？ [英] Is count(*) constant time in SQLite, and if not what are alternatives?

问题描述

我正在寻找一种最佳方法来计算大型（超过1500万行）表中的行数。根据我在此问题上发现的一些较早的帖子，从表中选择count（*）的天真方法显然是O（n）。 http://osdir.com/ml/sqlite-users/2010-07 /msg00437.html 。

I'm looking for the best way to count how many rows there are in a large (15 million+ rows) table. The naive way of select count(*) from table; is apparently O(n) according to a few older posts I've found on the matter, e.g. http://osdir.com/ml/sqlite-users/2010-07/msg00437.html.

是否存在一种恒定的时间机制来获取此信息，否则将无法获得简单的 select count（*）查询？

Is there a constant time mechanism to get this information, or failing that are there preferred alternatives to the straightforward select count(*) query?

推荐答案

SQLite对<$ c $有特殊的优化c> COUNT（*）不带 WHERE 子句，该子句将遍历表的B树页面并对条目进行计数，而无需实际加载记录。
但是，这仍然需要访问表的所有数据（大记录的溢出页除外），因此运行时仍为O（n）。

SQLite has a special optimization for COUNT(*) without a WHERE clause, where it goes through the table's B-tree pages and counts entries without actually loading the records. However, this still requires that all the table's data (except overflow pages for large records) is visited, so the runtime is still O(n).

SQLite不会在数据库中存储单独的记录计数，因为这会使所有更改变慢。

SQLite does not store a separate record count in the database because that would make all changes slower.

这篇关于在SQLite中count（*）是固定时间的吗？如果不是，那有什么替代方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！