Use _counts to speed up counts

[simonw]

Utility mechanism for taking advantage of the new _counts table from #212 would be nice.

These can trigger automatically if the _counts table exists, but since sqlite-utils needs to work against any existing database there should be a way of opting out of this optimization.

[simonw]

Idea: a db.cached_counts() method that returns a dictionary of data from the _counts table. Call it with a list of tables to get back the counts for just those tables.

[simonw]

Thought: maybe there should be a .reset_counts() method too, for if the table gets out of date with the triggers.

One way that could happen is if a table is dropped and recreated - the counts in the _counts table would likely no longer match the number of rows in that table.

[simonw]

    def cached_counts(self, tables=None):
        sql = "select [table], count from {}".format(self._counts_table_name)
        if tables:
            sql += " where [table] in ({})".format(", ".join("?" for table in tables))
        return {r[0]: r[1] for r in self.execute(sql, tables).fetchall()}

[simonw]

Here's the current .count property:

sqlite-utils/sqlite_utils/db.py

Lines 597 to 609 in 036ec6d

	class Queryable:
	def exists(self):
	return False
	def __init__(self, db, name):
	self.db = db
	self.name = name
	@property
	def count(self):
	return self.db.execute(
	"select count(*) from [{}]".format(self.name)
	).fetchone()[0]

It's implemented on Queryable which means it's available on both Table and View - the optimization doesn't make sense for views.

I'm a bit cautious about making that property so much more complex. In order to decide if it should try the _counts table first it needs to know:

• Should it be trusting the counts? I'm thinking a .should_trust_counts property on Database which defaults to True would be good - then advanced users can turn that off if they know the counts should not be trusted.
• Does the _counts table exist?
• Are the triggers defined?

Then it can do the query, and if the query fails it can fall back on the count(*). That's quite a lot of extra activity though.

[simonw]

Alternative implementation: provided db.should_trust_counts is True, try running the query:

select count from _counts where [table] = ?

If the query fails to return a result OR throws an error because the table doesn't exist, run the count(*) query.

[simonw]

Another option: on creation of the Database() object, check to see if the _counts table exists and use that as the default for a use_counts_table property. Also flip that property to True if the user calls .enable_counts() at any time.

[simonw]

Idea: a .execute_count() method that never uses the cache.

[simonw]

I'm having second thoughts about this being the default behaviour. It's pretty weird. I feel like HUGE databases that need this are rare, so having it on by default doesn't make sense.

[simonw]

So if you instantiate the Database() constructor with use_counts_table=True any access to the .count properties will go through this table - otherwise regular count(*) queries will be executed.