# Detecting Full Table Scans With SQLite

DevFeed: [Detecting Full Table Scans With SQLite](<https://devfeed.tech/articles/detecting-full-table-scans-with-sqlite-39006.md>)

Original publisher: [Read original article](<https://tenderlovemaking.com/2026/07/15/detecting-full-table-scans-with-sqlite/>)

Published: 2026-07-15T15:26:22Z

Content type: tutorial

Language: en

Sources: [Aaron Patterson](<https://devfeed.tech/sources/aaron-patterson.md>)

Topics: [SQLite](<https://devfeed.tech/topics/sqlite.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Development](<https://devfeed.tech/topics/development.md>), [Rails](<https://devfeed.tech/topics/rails.md>)

Tags: [database](<https://devfeed.tech/tags/database.md>), [index](<https://devfeed.tech/tags/index.md>), [query](<https://devfeed.tech/tags/query.md>), [rails](<https://devfeed.tech/tags/rails.md>), [sqlite](<https://devfeed.tech/tags/sqlite.md>), [test](<https://devfeed.tech/tags/test.md>)

## AI overview

This tutorial shows how to detect full table scans in SQLite by inspecting prepared-statement statistics after executing a query. It demonstrates checking full-scan steps, then adding an index to eliminate the scan, and discusses possible Rails integration for test or development warnings.

## Source excerpt

I'm at RubyConf this week, and it's great! I recently read that lobste.rs is now running on SQLite. One part from the post caught my attention: I wish we could say in a test, "Fail if you encounter any full table scans". Which would have caught the perf issues we experienced during the first deploy. SQLite collects information about prepared statements and exposes those statistics though an API. The upshot of this is that we can tell whether a statement did a full table scan after executing the statement without using an EXPLAIN. Here's an example program that demonstrates detecting a query did a full table scan: db = SQLite3::Database.new(":memory:") db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)") # Insert a bunch of records 1_000.times do |i| db.execute("INSERT INTO users (name, age) VALUES (?, ?)", ["user#{i}", i % 100]) end def query(db) # Prepare a statement and query it stmt = db.prepare("SELECT * FROM users WHERE age = ?") stmt.bind_param(1, 42) stmt.to_a # Check the number of full scan steps to detect full table scan fullscan_steps = stmt.stat(:fullscan_steps) puts "fullscan_steps: #{fullscan_steps}" if fullscan_steps > 0 puts " => query performed a full table scan" else puts " => no full table scan" end end # No index, so we'll see a full table scan query(db) # Create an index db.execute("CREATE INDEX idx_users_age ON users(age)") # Added an index, so no full table scan query(db) Feels like we could integrate this in to Rails and warn or raise in test / development. I'm not sure if we'd want to check this all the time in production, but maybe it would be fine?