Mechanism for maintaining cache of table counts using triggers

[simonw]

Counting all of the rows in a large table is expensive - this is one of the main causes of performance problems in Datasette when running against large databases.

Carefully constructed SQL triggers could be used to maintain accurate cached counts for a table, by incrementing and decrementing a counter every time a row is inserted or deleted.

sqlite-utils already has a mechanism for creating triggers for FTS - the table.enable_fts(..., create_triggers=True) method. How about a similar mechanism for setting up triggers to maintain a count of table rows?

[simonw]

Here's a prototype:

with db.conn:
    db.conn.executescript("""
CREATE TABLE IF NOT EXISTS [_counts] ([table] TEXT PRIMARY KEY, [count] INTEGER DEFAULT 0);
CREATE TRIGGER IF NOT EXISTS [Street_Tree_List_counts_ai] AFTER INSERT ON [Street_Tree_List] BEGIN
    INSERT OR REPLACE INTO _counts
        VALUES ('Street_Tree_List', COALESCE(
            (SELECT count FROM _counts
                WHERE [table]='Street_Tree_List'),
            0) + 1);
END;
CREATE TRIGGER IF NOT EXISTS [Street_Tree_List_counts_ad] AFTER DELETE ON [Street_Tree_List] BEGIN
    INSERT OR REPLACE INTO _counts
        VALUES ('Street_Tree_List', COALESCE(
            (SELECT count FROM _counts
                WHERE [table]='Street_Tree_List'),
            0) - 1);
END;
INSERT OR REPLACE INTO _counts VALUES ('Street_Tree_List', (select count(*) from [Street_Tree_List]));
""")