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.

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.

Here's the current .count property:

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.

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.

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.

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

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.

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.