# Making Historical Figure Search 500x Faster Making Historical Figure Search 500x Faster *From 1500 milliseconds to 3 milliseconds: Implementing inverted indexing at CBDB* [中文版](https://chinesecbdb.hsites.harvard.edu/ranglishirenwusousuokuai-500-bei) --- CBDB currently contains approximately **650,000** historical figures and **1 million** name records. Searching for names like "蘇軾" (Su Shi) or "安石" (Anshi) used to take 1-2 seconds. When conducting extensive research, these delays accumulate into significant time costs. The CBDB technical team redesigned the name indexing approach, reducing query time from 1,500 milliseconds to 3 milliseconds—**a 500× improvement**. The core method involves building an **inverted index** based on Chinese naming conventions and supporting **traditional/simplified Chinese interoperability**. --- ## Why Was Search So Slow? Traditional queries use: `WHERE name LIKE '%蘇軾%'` This "containment" query cannot leverage database B-tree indexes, forcing a scan of all 1 million records—taking 1-1.5 seconds. Benchmark results: SortQueryTime"安石" 1,374 ms "蘇軾" 1,540 ms **The bottleneck isn't computational power—it's that data organization doesn't match Chinese name search patterns.** --- ## Why Not Use Full-Text Search? While Elasticsearch, PostgreSQL FTS, and MySQL ngram can accelerate Chinese search, they face CBDB constraints: - **MariaDB 10.3** lacks ngram support (compatibility issue) - **PostgreSQL FTS** locks into a single database - **Elasticsearch** requires high maintenance overhead **CBDB's principles**: Must be database-agnostic, long-term maintainable, and use standard SQL. Therefore, we chose to **hand-craft an inverted index**. --- ## What Is an Inverted Index? **Traditional approach** (forward index): `Person ID → Name` **Inverted approach**: `Name component → Multiple person IDs` Based on actual CBDB data: `安石 → [Chen Anshi, Pei Anshi, Li Anshi, Lu Anshi, Yang Anshi, Wu Anshi, Shao Anshi, ...]       (165 people total) 軾   → [Su Shi, Jiang Jishi, Zhang Shi, Lin Shi, Zhou Shi, ...]` When searching, the database directly queries this "reverse relationship table" without scanning all name records. --- ## Chinese Name Decomposition We use **suffix splitting**: `王安石 → [王安石, 安石, 石] 介甫   → [介甫, 甫] 半山   → [半山, 山]` This transforms: `LIKE '%石%'  -- Cannot use index` Into: `LIKE '石%'   -- Fully uses B-tree index` **This is the key to performance improvement.** --- ## Traditional/Simplified Interoperability Using OpenCC character mappings to build simplified suffixes: `蘇軾 → [蘇軾, 軾]  (Traditional) 苏轼 → [苏轼, 轼]  (Simplified)` **Query results**: - ✅ Input "蘇軾" → Match - ✅ Input "苏轼" → Also matches - ✅ Input "軾" / "轼" → Both match **No need to switch input methods—traditional and simplified are handled uniformly.** --- ## Implementation and Data Scale The inverted index table CBDB\_\_NAME\_FTS is built with standard SQL: `CREATE TABLE CBDB__NAME_FTS (    search_term VARCHAR(100),  -- Suffix    c_personid INT,            -- Person ID    full_name VARCHAR(100),    is_simplified TINYINT(1),    INDEX (search_term, c_personid) );` **Data metrics**: SortItemValuePeople 650,000 Names 1 million Inverted records ~3 million Storage 250-350 MB Rebuild time 15-30 minutes **Compatible** with MySQL, MariaDB, PostgreSQL, SQLite. --- ## Performance Results SortQuery TypeBeforeAfterImprovement"石" ~1500 ms 5 ms **300×** "安石" ~1374 ms 3 ms **458×** "王安石" ~1540 ms 3 ms **513×** Person ID ~1500 ms 2 ms **750×** **Average improvement: ~500×** For researchers, search results now appear "instantaneously." --- ## No Change to User Search Habits All searches complete in milliseconds: - ✅ "王安石" - ✅ "安石" - ✅ "石" - ✅ "蘇軾" / "苏轼" - ✅ "介甫" - ✅ "歐陽" (compound surnames) Search logic remains identical to before—just hundreds of times faster. --- ## Technical Design Principles 1. **Data organization determines performance ceiling**: Inverted indexing trades space for time 2. **Portability**: Independent of specific database features 3. **Long-term maintainability**: Rebuildable and updatable in 10 years 4. **Traditional/simplified support at the data layer**, not as an add-on patch --- ## Conclusion: Millisecond-Level CBDB Queries Inverted indexing moved CBDB name search from seconds to milliseconds, dramatically improving the research experience. **Whether researchers input traditional or simplified characters, full names or fragments, the system returns results instantaneously.** With this faster foundation, future enhancements can explore pinyin search, variant character handling, and semantic queries. --- ## Appendix: Further Reading ### Technical Documentation For detailed design principles, implementation details, and code: - [**Complete Technical Documentation**](https://github.com/cbdb-project/cbdb-online-main-server/blob/develop/NAME_SEARCH_PERFORMANCE_IMPROVEMENT.md) - Includes decomposition algorithms, memory optimization, transaction management, etc. - [**Command-Line Tools**](https://github.com/cbdb-project/cbdb-online-main-server/blob/develop/NAME_SEARCH_COMMANDS.md) - Index rebuilding, traditional/simplified mapping import, and operation guides - [**Other Performance Optimizations**](https://github.com/cbdb-project/cbdb-online-main-server/blob/develop/CODES_PERFORMANCE.md) - Other CBDB system optimizations ### Core Code - app/Console/Commands/RebuildNameSearchIndex.php - Index rebuild command - app/Repositories/BiogMainRepository.php - Search query logic - database/migrations/\*\_create\_internal\_name\_search\_tables.php - Table schema definitions ### Key Metrics SortMetricValueQuery time (before) 1500ms Query time (after) 3ms Performance gain 500× Inverted records 3 million Index storage 250-350 MB People covered 650,000 Names covered 1 million Traditional/Simplified mappings 7,000+ characters ### Edge Cases - **Compound surnames**: For surnames like Ouyang (歐陽) and Sima (司馬), indexing doesn't identify surnames—names are split as generic strings (see full documentation) - **Polysemous characters**: Uses OpenCC character-by-character mapping without contextual disambiguation (e.g., "干/幹/乾" uses default correspondences) - **Foreign names**: Not decomposed; stored as complete words - **Single-character suffixes**: Allowed (e.g., "甫"), not filtered out --- *Written by Frank Lin, November 2025* *For technical questions or suggestions, please contact us via* [*GitHub Issues*](https://github.com/cbdb-project/cbdb-online-main-server/issues)