Turso Rows Read Too High? How One `COUNT(DISTINCT)` Read 10k Rows to Count 200 Staff

One Scheduled today KPI looked perfect: about 200 unique people. Turso’s Top Queries told a different story — roughly 10,300 rows read per call and more than 4 million rows read in a month from that single statement. The UI was fine. Turso’s rows-read metric was not.

I am Prakash Niraula, a System Engineer at AI Kensetsu Co., Ltd in Sakai, Osaka. I also ship workforce products like GOLO CRM. This is a production story from a Next.js admin on Drizzle + Turso (libSQL / SQLite): punches, site schedules, leave badges — the usual operations dashboard. We caught the cost after about a month of Top Queries data, before the table (and the bill) got much worse.

Why are my Turso rows read so high?

The KPI answers a simple product question: how many unique people are scheduled for today?

The product felt snappy. React Query cached the result. A short server cache reduced repeats. Then we sorted Turso Top Queries by Total Rows Read.

arithmetic (Turso dashboard)

395 calls × ~10,300 Turso rows read / call
≈ 4.07 million Turso rows read

The query returned a count of about 200. Turso reported roughly the whole table as rows read on every execution. No huge join. No obvious UI stall. Twelve words of SQL:

sql — scheduled today

SELECT count(DISTINCT staff_id)
FROM timesheets
WHERE work_date = ?;

The problem was not the answer. It was the database work Turso associated with getting that answer.

What we blamed first

Production debugging rarely starts in the right place. We looked at:

• Chatty timetable loaders — real waste, not this spike.
• React Query invalidation — real refetch issue, not Turso’s #1 rows-read query.
• COUNT(DISTINCT) itself — tempting, but changing the aggregate does not fix a bad access path.
• Redis / materialized counters — overkill if the SQLite plan can be indexed.

The useful move: start with the Turso metric, pin the exact SQL, then inspect the SQLite plan. Do not optimize the UI before you know what the database is doing.

The smoking-gun SQL

It sat behind a ~30 second Next.js cache. Caching reduces how often you pay. It does not change the access path of one miss. If a cache miss still makes Turso report ~10,300 rows read, you still pay that on every miss.

Indexes we already had

We were not starting from zero.

Why COUNT(DISTINCT) was not the villain

These two queries are different products:

sql — count rows (assignments)

SELECT count(*)
FROM timesheets
WHERE work_date = ?;

sql — count unique people

SELECT count(DISTINCT staff_id)
FROM timesheets
WHERE work_date = ?;

If one person can have multiple timesheet rows, COUNT(*) counts assignments. We needed people. Changing the aggregate does not magically make work_date searchable. Fix the access path first.

How we investigated it

1. Sort Turso Top Queries by Total Rows Read

Wall-clock time was not the first clue. The file was small enough that the query felt fine in the app. Turso’s rows-read column is what made a tiny dashboard count look expensive.

2. Compare the KPI with table size

observation

Distinct staff for today:     ~200     (application result)
Total rows in timesheets:     ~10,300  (table size)
Turso rows read / call:       ~10,300  (Turso dashboard — not SQLite EXPLAIN)

The KPI cared about one date. Turso reported rows read close to the full table. That suggested the statement was not using a selective date-leading path. It did not prove SQLite physically walked 10,300 heap rows — only that Turso accounted ~10,300 reads for that statement.

3. Verify live indexes (not just schema.ts)

An index in a Drizzle schema or a migration file is not proof it exists on the Turso database you are querying.

sql — live sqlite_master

SELECT name, sql
FROM sqlite_master
WHERE type = 'index'
  AND tbl_name = 'timesheets';

4. Run EXPLAIN QUERY PLAN (SQLite / libSQL)

sql — plan before the new index

EXPLAIN QUERY PLAN
SELECT count(DISTINCT staff_id)
FROM timesheets
WHERE work_date = ?;

query plan (SQLite)

SCAN timesheets

The fix: covering index on (work_date, staff_id)

The statement needs work_date (filter) and staff_id (distinct). Order matters.

sql — covering index

CREATE INDEX timesheets_work_date_staff_index
ON timesheets (work_date, staff_id);

• work_date first — matches WHERE work_date = ?.
• staff_id second — the index holds the column the aggregate needs, so this query can be covering (index-only).

sql — plan after

EXPLAIN QUERY PLAN
SELECT count(DISTINCT staff_id)
FROM timesheets
WHERE work_date = ?;

query plan (SQLite)

SEARCH timesheets
USING COVERING INDEX timesheets_work_date_staff_index
(work_date=?)

Then we re-ran the same SQL shape in production and compared Turso’s reported rows-read for that statement — not a SQLite CLI counter.

Before / after (Turso dashboard vs SQLite plan)

turso arithmetic after

395 × ~200 Turso rows read
≈ 79,000 Turso rows read

Same KPI. Same SQL meaning. Same cache TTL. Same call count. Different access path. Turso’s dashboard moved from ~10,300 to ~200 per call. I still describe that drop as Turso rows read, because that is the meter we used.

Why the covering index helped

query shape vs index shape

WHERE work_date = ?
        ↓
INDEX (work_date, staff_id)   -- date locates; staff_id covers DISTINCT

Pattern: put columns that find the rows first, then columns the query must read. For this statement that is (work_date, staff_id) — not (staff_id, work_date).

reported reduction (Turso)

1 - (200 / 10,300) ≈ 98%
≈ 50× fewer Turso rows read per execution

Other Top Queries we cleaned up

Rank 1 was the surprise. It was not the only statement worth looking at.

We also cut call-frequency waste: settings fetched on every navigation, repeated staff-by-id lookups, window-focus refetches, timetable actions that could be one payload. Cheap-per-call × 50,000 calls is still a large Turso total. Look at both:

cost model

Turso total rows read ≈ (Turso rows read / call) × (number of calls)

People also ask

Why did Turso show thousands of rows read for a count of 200?

Because the result is not Turso’s rows-read meter. The KPI returned ~200 unique staff. Turso reported ~10,300 rows read per execution. SQLite’s plan was SCAN timesheets. After the covering index, Turso’s meter dropped and the plan became an indexed search. If those two ever stop lining up, trust the labels: Turso shows Turso; EXPLAIN QUERY PLAN shows the path.

Was COUNT(DISTINCT) the problem?

Not by itself. We needed unique people. The missing piece was a date-leading index. Do not rewrite COUNT(DISTINCT staff_id) to COUNT(*) unless you want to count rows instead of people.

Will COUNT(*) fix high Turso rows read?

Not if the access path is still a scan. The useful change was the index:

sql

CREATE INDEX timesheets_work_date_staff_index
ON timesheets (work_date, staff_id);

What is a covering index?

An index that contains every column that particular statement needs. Here, (work_date, staff_id) covers both the filter and the distinct column, so SQLite can SEARCH without fetching table rows for those columns.

Does composite index column order matter?

Yes. (work_date, staff_id) and (staff_id, work_date) are not interchangeable for WHERE work_date = ?.

How do I verify the fix?

1. Confirm the index on the live Turso database via sqlite_master.
2. Run EXPLAIN QUERY PLAN on the exact SQL — you want SEARCH and ideally COVERING INDEX.
3. Compare Turso Top Queries for the same SQL shape. In our case Turso showed ~10,300 → ~200. That is still Turso’s number.

Do Next.js cache or React Query solve this?

They cut executions. An index cuts work per execution. You want both. A cache can hide a bad plan until it expires.

Checklist: reducing Turso / SQLite cost

1. Open Turso Top Queries; sort by Total Rows Read (that column is Turso).
2. Find statements where Turso rows read dwarf the result you care about.
3. Verify live indexes; do not trust migration files alone.
4. Run EXPLAIN QUERY PLAN on the exact SQL (SQLite/libSQL).
5. If the plan is SCAN and a selective predicate exists, fix index shape — leading column = filter.
6. Consider a covering index when the query only needs indexed columns.
7. Do not swap COUNT(DISTINCT …) for COUNT(*) unless the product meaning allows it.
8. Keep Turso rows-read and SQLite plans as related but distinct evidence.
9. After deploy, compare the same SQL shape on Turso before vs after.
10. Then cut call frequency: refetches, navigation loaders, duplicated loaders.

Closing

The SQL was twelve words. The KPI still returned ~200 people. Turso reported ~10,300 rows read per call until a covering index changed the plan from SCAN to SEARCH … COVERING INDEX, and Turso’s average moved to ~200.

The lesson was not “never use COUNT(DISTINCT),” “SQLite is slow,” or “add Redis.” It was: an index can exist and still be the wrong index for the query you care about.

When a KPI is correct but Turso’s rows-read metric looks insane, do not guess from the UI. Pin the SQL. Check live indexes. Read the SQLite plan. Then compare Turso’s meter before and after — and if the two systems disagree, say so: Turso shows X; SQLite planned Y.

We caught this on about a month of dashboard data, while the timesheet table was still ~10k rows. That is the cheap time to fix it. For attendance and workforce dashboards on SQLite, libSQL, or Turso: the query can be tiny. The access path can still be expensive.

If you are building the same class of system — rosters, punches, multi-site schedules — look at GOLO CRM or contact me with the query that is quietly topping your Turso report.

Talk to Prakash about SQLite / Turso performance →