search

simonw / datasette

An open source multi-tool for exploring and publishing data
https://datasette.io
Apache License 2.0
9.59k stars 691 forks source link

Take advantage of sqlite-utils cached table counts, if available #1183

Open simonw opened 3 years ago

simonw commented 3 years ago

sqlite-utils 3.2 now has a mechanism for creating a _counts table with triggers to maintain counts for individual tables. Datasette could look for this table and use it for counts, dramatically speeding up places that currently suffer from slow counts. Refs #859.

https://sqlite-utils.datasette.io/en/stable/python-api.html#cached-table-counts-using-triggers

simonw commented 3 years ago

So how would this work?

I think I'm going to automatically use these values if the _counts table exists, unless a ignore_counts_table boolean setting has been set. I won't bother looking to see if the triggers have been created.

simonw commented 3 years ago

Should Datasette have subcommands for this? datasette enable-counts data.db and datasette disable-counts data.db and datasette reset-counts data.db commands?

Maybe. The sqlite-utils CLI tool could be used here instead, but that won't be easily available if Datasette was installed as a standalone binary or using brew install datasette or pipx install datasette.