In this lesson, you will learn how to inspect and refine your SQL queries to avoid costly full table scans, ensuring your embedded database remains responsive even on resource-constrained hardware.
The Problem: Full Table Scans
When you run a query like SELECT * FROM users WHERE email = 'test@example.com', the database engine must decide how to find that record. If no index exists on the email column, the engine performs a Full Table Scan. It reads every single row in the database file from disk into memory just to find one match. In embedded systems—where I/O and memory are precious—this is the primary cause of sluggish performance.
Interpreting the Plan
To see how your engine is working, use the EXPLAIN QUERY PLAN command prefixing your SQL.
If you see an output like:
SCAN TABLE users
This confirms the database is scanning the entire table. You want to see:
SEARCH TABLE users USING INDEX idx_users_email
The word SEARCH combined with USING INDEX tells you that the engine is jumping directly to the relevant disk pages, bypassing the need to read the entire table.
Rewriting for Optimization
If you identify a SCAN, you have two main paths to optimize:
- The Schema Path: Add an index on the column being filtered:
CREATE INDEX idx_users_email ON users(email); - The Query Path: Ensure your query logic allows index usage. For example, avoid wrapping columns in functions. If you write
WHERE UPPER(email) = 'TEST@EXAMPLE.COM', the engine cannot use a standard index because it must transform every row's email before comparing it. Instead, store the data in the desired case or use a case-insensitive collation.
By checking the execution plan, you stop guessing why a query is slow and start identifying exactly which parts of your schema need indexing or which query clauses are forcing the engine to work harder than necessary.
Key Takeaway: Always use EXPLAIN QUERY PLAN to verify that your queries are performing an index-based SEARCH rather than an expensive SCAN.