Fast Searching 4 Million Patent Records with FTS5

Introduction: The Limitations of LIKE Search When searching for "battery" in PatentLLM's patent database (4 million records), results were slow to appear. A full table scan using LIKE '%battery%' was not practical in terms of speed. There were three main problems with LIKE search: Performance degradation due to full table scans Inability to handle variations in word endings (battery / batteries) Difficulty with complex searches like "battery OR lithium NOT sodium" What is FTS5? FTS5 is a full-text search engine built into SQLite. It pre-builds an inverted index, allowing it to instantly retrieve relevant rows from keywords. Key benefits: Fast searching with an inverted index BM25 ranking: Automatic scoring based on relevance to search terms Native support for complex searches: battery OR lithium NOT sodium can be written in a single line Implementation Steps Building the FTS5 Table -- FTS5仮想テーブルを作成 CREATE VIRTUAL TABLE cases_fts USING fts5 ( raw_case_name, summary, analysis_json ) ; --