This repository contains some examples for reducing the billed usage of a Turso database (in terms of rows read) for certain types queries that might normally require an expensive full table scan. The techniques here also improve the overall performance of the query as performed by the underlying SQLite query engine.
Read the companion blog post that discusses the reasoning behind these optimizations.
The examples here boil down to two strategies:
- Use an index on fields used with filtering, ordering,
min
, ormax
- Use SQLite triggers to pre-compute aggregate values, such as
sum
andavg
The SQL scripts here are designed to be fed directly into the Turso CLI or sqlite3 CLI for execution. For example, to create the example table and it starting data:
turso db shell $DBNAME < 01_table_and_data.sql
All of the examples here assume the same starting point: a table and 4 rows. This table is very simplistic and naive, but offers a starting point to understand the benefits of the optimizations below.
A query with a filter causes a full table scan. Add an index on the columns used for filtering to reduce the number of reads to only the number of rows returned by the query.
SQL: 03_index_for_min_max_order_limit.sql
A query that requires an ordering of data on a column causes a full table scan.
Add an index on that column to reduce the number of reads to only the number of
row values used by the query. For example, the min()
function requires
scanning each and every row in the table to find the lowest value, but an index
allows SQLite to find the minimum value with a single row read.
SQL: 04_triggers_for_table_row_count.sql
A query using count()
causes a full table scan. Use triggers to maintain a
running count of rows in another table when a row is inserted or deleted from
the original table. The other table can then be queried for the count, requiring
only a single row read.
SQL: 05_triggers_for_filtered_row_count.sql
Similar to above, a filtered query using count()
causes a full table scan.
Even with an index on the column used in the filter, a scan of the matching rows
is still required to compute a count. Use triggers to maintain running unique
value counts in another table whenever a rows is inserted, updated or deleted
from the original table. The other table can then be queried for the filtered
count, requiring only a single row read.
SQL: 06_triggers_for_column_avg.sql
A query using avg()
causes a full table scan. Use triggers to maintain a
running average in another table of the values in a column when a row value is
inserted, updated, or deleted from the original table. The other table can then
be queried for the average, requiring only a single row read.
The nodejs program under monitor-usage was useful for actively monitoring the actual usage of the test database as SQL commands were executed against it.