Making Historical Figure Search 500x Faster

Making Historical Figure Search 500x Faster

From 1500 milliseconds to 3 milliseconds: Implementing inverted indexing at CBDB

中文版

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:

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:

Compatible with MySQL, MariaDB, PostgreSQL, SQLite.

Performance Results

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
  • Includes decomposition algorithms, memory optimization, transaction management, etc.
• Command-Line Tools
  • Index rebuilding, traditional/simplified mapping import, and operation guides
• Other Performance Optimizations
  • 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

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