In this lesson, you will learn why choosing between pattern matching and schema-based indexing is a critical performance trade-off in embedded database environments.
The Cost of Flexibility
When querying text in a database, you often choose between LIKE (pattern matching) and standard B-Tree indexes. Pattern matching—specifically using wildcards at the start of a string like LIKE '%term'—forces the database to perform a full table scan. In a small embedded database, this might seem negligible, but as your file size grows, the engine must load every single page into memory to inspect every row for that pattern.
The Indexing Advantage
Indexing relies on a pre-sorted tree structure. When you perform an equality search (WHERE name = 'Alice'), the engine traverses the B-Tree in logarithmic time—it knows exactly which page contains the data. However, standard indexes are designed for exact matches or prefix matches (name LIKE 'Ali%').
Consider the performance difference in a Users table with 100,000 records:
-- Forced Full Table Scan
SELECT * FROM Users WHERE bio LIKE '%developer%';
-- Efficient Index Seek (if an index exists on 'bio')
SELECT * FROM Users WHERE bio = 'software developer';
In the first query, the database is "blind"; it must check the bio column of every record. In the second, the index acts like a map, skipping the vast majority of the data.
When Patterns Outpace Indexes
If you strictly need flexible pattern matching (like searching for substrings within a long text), standard indexing falls short. This is where you might need to move beyond basic schema indices and utilize tools like Full-Text Search (FTS) Integration, which we previously explored. FTS creates a specialized "inverted index" that maps every word to its row location, allowing for rapid pattern matching that standard B-Tree indices simply cannot replicate.
In an embedded system, every CPU cycle and I/O operation counts. If your query frequency is high, always favor schema-based indices over patterns. If your requirements demand flexible substring searches, implement a specialized search module rather than forcing the engine to scan your entire storage file repeatedly.
Key Takeaway: While pattern matching offers broad search flexibility, it forces full table scans that degrade performance; always prefer B-Tree indexed equality or prefix matches, and use specialized FTS modules for complex substring searches.