# rds-search

Part of **RDS**

# ApsaraDB RDS Text and Vector Search Troubleshooting Guide

## Problem Index

| Problem | Symptom | Severity | Solution Summary |
|--------|--------|---------|------------------|
| Full-text search returns no results for short words | Queries with words shorter than default token size return empty results | Medium | Adjust `ft_min_word_len` (MyISAM) or `innodb_ft_min_token_size` (InnoDB) and rebuild index |
| Full-text index not working after table engine change | Full-text queries fail after converting from MyISAM to InnoDB | High | Rebuild the full-text index explicitly after engine conversion |
| Poor performance compared to LIKE queries | Full-text search slower than equivalent `LIKE '%term%'` query | Medium | Verify index usage, adjust `ft_query_expansion_limit`, and consider query structure |

## Problem Details

### Problem 1: Full-text search returns no results for short words

**Symptoms**
- Error message: No explicit error; query returns empty result set unexpectedly
- Behavior: Searches for terms like "go", "AI", or "DB" yield no matches even when data contains these words
- Context: Occurs when using default full-text search settings on tables with short keywords

**Root Cause**
- MySQL full-text search ignores tokens shorter than the minimum token size:
  - For MyISAM tables: controlled by `ft_min_word_len` (default: 4)
  - For InnoDB tables: controlled by `innodb_ft_min_token_size` (default: 3)
- Words below this threshold are excluded from the full-text index during creation

**Solution**
1. Determine your storage engine:
   ```sql
   SHOW CREATE TABLE your_table_name;
   ```
2. For **MyISAM** tables:
   - Modify the RDS parameter group to set `ft_min_word_len = 2` (or desired value ≥ 1)
   - Rebuild the full-text index:
     ```sql
     REPAIR TABLE your_table_name QUICK;
     ```
3. For **InnoDB** tables:
   - Modify the RDS parameter group to set `innodb_ft_min_token_size = 2` (valid range: 1–32)
   - Drop and recreate the full-text index:
     ```sql
     ALTER TABLE your_table_name DROP INDEX your_ft_index;
     ALTER TABLE your_table_name ADD FULLTEXT(your_column);
     ```

**Verification**
- Run a test query with a short word known to exist in the data:
  ```sql
  SELECT * FROM your_table_name WHERE MATCH(your_column) AGAINST('go');
  ```
- Expected: Returns rows containing the word "go"

### Problem 2: Full-text index not working after table engine change

**Symptoms**
- Error message: `Can't find FULLTEXT index matching the column list`
- Behavior: Full-text queries that worked on MyISAM fail after converting table to InnoDB
- Context: Occurs when migrating tables from MyISAM to InnoDB without reindexing

**Root Cause**
- Full-text indexes are storage-engine specific and not automatically migrated during `ALTER TABLE ... ENGINE=InnoDB`
- The index metadata becomes invalid after engine conversion

**Solution**
1. Confirm the current engine and index status:
   ```sql
   SHOW CREATE TABLE your_table_name;
   SHOW INDEX FROM your_table_name;
   ```
2. Drop the existing full-text index:
   ```sql
   ALTER TABLE your_table_name DROP INDEX your_ft_index_name;
   ```
3. Recreate the full-text index under InnoDB:
   ```sql
   ALTER TABLE your_table_name ADD FULLTEXT(your_column_list);
   ```
4. If using multiple columns, specify them explicitly:
   ```sql
   ALTER TABLE your_table_name ADD FULLTEXT(col1, col2);
   ```

**Verification**
- Execute a sample full-text query:
  ```sql
  SELECT * FROM your_table_name WHERE MATCH(col1, col2) AGAINST('test');
  ```
- Expected: Query executes without error and returns relevant matches

### Problem 3: Poor performance compared to LIKE queries

**Symptoms**
- Behavior: Full-text search takes longer than an equivalent `LIKE '%keyword%'` query
- Context: Observed during benchmarking or high-load scenarios, especially with small datasets

**Root Cause**
- Full-text search has overhead for parsing, ranking, and relevance scoring
- Query expansion (enabled by default in natural language mode) increases computational cost
- On very small tables, the index lookup overhead may exceed sequential scan efficiency
- Default `ft_query_expansion_limit` (20) may cause excessive document lookups during expansion

**Solution**
1. Ensure the full-text index is actually being used:
   ```sql
   EXPLAIN SELECT * FROM your_table_name WHERE MATCH(col) AGAINST('term');
   ```
   - Look for `FULLTEXT` in the `Extra` column
2. Reduce query expansion cost by lowering `ft_query_expansion_limit` in parameter group (e.g., to 10)
3. Use boolean mode to disable expansion if relevance ranking isn’t needed:
   ```sql
   SELECT * FROM your_table_name WHERE MATCH(col) AGAINST('+term' IN BOOLEAN MODE);
   ```
4. For prefix searches, note that full-text does **not** support leading wildcards (`*term`) — use `LIKE` if required

**Verification**
- Compare execution times before and after changes using:
  ```sql
  SET profiling = 1;
  SELECT * FROM your_table_name WHERE MATCH(col) AGAINST('test');
  SHOW PROFILES;
  ```
- Expected: Reduced query duration and consistent use of full-text index

## FAQ

**Q: How do I check if my full-text index is properly built?**  
A: Use `SHOW INDEX FROM table_name` and verify a row with `Index_type = FULLTEXT`. Additionally, run `INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE` (for InnoDB) after enabling `innodb_ft_aux_table`.

**Q: What permissions are required to manage full-text indexes in RDS for MySQL?**  
A: The user needs `INDEX` privilege on the table to create or drop full-text indexes. This is typically granted via standard database user roles in RDS.

**Q: Why doesn’t full-text search work with Chinese text by default?**  
A: MySQL’s built-in parser tokenizes by whitespace and punctuation, which doesn’t align with Chinese word boundaries. Consider using n-gram tokenization (set `ngram_token_size` in parameter group) or preprocessing text into space-separated tokens.

**Q: Can I use full-text search with partitioned tables?**  
A: Yes, but only with InnoDB tables in MySQL 5.7.25+ and 8.0.15+. MyISAM does not support full-text indexes on partitioned tables.

**Q: How do I enable debug logging for full-text operations?**  
A: Full-text operations don’t have dedicated debug logs in RDS, but you can monitor slow queries via the slow query log (enable `slow_query_log` in parameter group) and analyze execution plans with `EXPLAIN`.