MariaDB InnoDB Fulltext Indexing, ft_min_word_len & Urdu Search in Pakistan

E-commerce marketplaces, classifieds portals (OLX-style), news publishers, and digital legal databases in Pakistan rely heavily on in-database search functionality. However, developers frequently implement search queries using unindexed SQL substring matching:

SELECT * FROM products WHERE product_name LIKE '%kurta%' OR description LIKE '%lawn%';

Under high concurrency, leading-wildcard LIKE '%...%' queries force MariaDB to perform Full Table Scans, reading every single row and disk block sequentially. When an inventory catalog grows to 500,000+ items, a single user search takes 3 to 12 seconds, pegging database CPU cores at 100% and bringing the shopping platform to a grinding halt.

While MariaDB’s InnoDB storage engine includes built-in FULLTEXT Search (FTS) indexing capable of returning search results in sub-millisecond time, default MariaDB configurations fail dramatically in Pakistani bilingual contexts:

1. Minimum Token Size (innodb_ft_min_token_size = 3): Ignores 2-letter search terms commonly used in Pakistan (e.g. “AC”, “TV”, “5G”, “HP”, “Mi”).
2. Aggressive Stopwords: Automatically discards common English words that may form crucial product model names.
3. Urdu / UTF-8 Multi-Byte Character Parsing: Standard whitespace parsers can struggle with unsegmented Urdu text without explicit collation and collation mapping.

By deploying on bare-metal Dedicated Servers and properly configuring InnoDB Fulltext parameters, custom token boundaries, and stopword overrides, database engineers can achieve sub-2ms search queries across millions of English and Urdu records.

---

The Architecture: Full Table Scan vs. InnoDB Inverted FTS Index

To understand why FULLTEXT indexing is exponentially faster than LIKE:

Searching for "Lawn Kurta":
===================================================================================
1. Substring Query (LIKE '%Lawn%'):
   - Must inspect every column of every row across 500,000 rows.
   - Zero index utilization!
   - Disk Reads: 2.4 GB of data scanned!
   - Execution Time: 8,450 milliseconds!

2. InnoDB FULLTEXT Index (MATCH ... AGAINST):
   - InnoDB maintains an "Inverted Index" (B-tree mapping words to document IDs).
   - Word: "lawn"  ──> Document IDs: [42, 891, 1204, 98402]
   - Word: "kurta" ──> Document IDs: [1204, 34091, 98402]
   - Evaluates set intersection in memory!
   - Execution Time: 1.8 milliseconds! (Over 4,000x faster!)
===================================================================================

---

Step 1: Auditing Current Fulltext Token Ceilings

Check the active token size limits in MariaDB:

SHOW GLOBAL VARIABLES LIKE '%ft_min%';

Typical default output:

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| ft_min_word_len            | 4     | -- Legacy MyISAM limit
| innodb_ft_min_token_size   | 3     | -- Default InnoDB limit (Blocks 2-letter queries!)
+----------------------------+-------+

Under innodb_ft_min_token_size = 3, searching for “AC” (Air Conditioner), “TV”, “HP” laptop, or 2-letter Urdu acronyms returns 0 results, leading customers to assume the product is out of stock!

---

Step 2: System-Wide Configuration in /etc/my.cnf.d/server.cnf

To allow 2-letter search terms and optimize fulltext caching memory in MariaDB, edit /etc/my.cnf.d/server.cnf:

[mariadb]
# /etc/my.cnf.d/server.cnf
# NextGen Pakistan - High-Performance Fulltext & Bi-Lingual Search Profile

# 1. Lower Minimum Token Size to 2 Characters
# Allows searches for "AC", "TV", "5G", "Mi", "LG", etc.
innodb_ft_min_token_size = 2
ft_min_word_len = 2

# 2. Maximum Token Size
# Default is 84; 32 is sufficient for virtually all human words
innodb_ft_max_token_size = 32

# 3. Dedicated In-Memory Fulltext Cache Size
# Allocates RAM for building fulltext indexes before flushing to disk
innodb_ft_cache_size = 32M

# 4. Total In-Memory Fulltext Index Processing Limit
innodb_ft_total_cache_size = 256M

# 5. Disable Default English Stopwords or Provide Custom Table
# Prevents MariaDB from discarding words like "about", "new", "one"
innodb_ft_enable_stopword = OFF

# 6. Default Server Collation for Proper Urdu / Arabic Multi-Byte Sorting
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Note: Because innodb_ft_min_token_size is a static startup variable, applying it requires a MariaDB restart:

systemctl restart mariadb

---

Step 3: Creating and Rebuilding the FULLTEXT Index

After changing innodb_ft_min_token_size, existing fulltext indexes must be rebuilt so that older rows are indexed with the new 2-character token rule:

-- 1. Create a Fulltext Index on single or combined columns
ALTER TABLE products ADD FULLTEXT INDEX idx_ft_products (product_name, description);

-- 2. If the index already existed, drop and re-add it to rebuild tokens:
ALTER TABLE products DROP INDEX idx_ft_products;
ALTER TABLE products ADD FULLTEXT INDEX idx_ft_products (product_name, description);

To optimize the index structure in the background:

SET GLOBAL innodb_optimize_fulltext_only = 1;
OPTIMIZE TABLE products;
SET GLOBAL innodb_optimize_fulltext_only = 0;

---

Step 4: Writing Performant Boolean Mode Search Queries

Query the fulltext index using MATCH(...) AGAINST(...) IN BOOLEAN MODE. Boolean mode allows operators like + (mandatory word), - (exclude word), and * (wildcard suffix):

-- Search for products matching both "Lawn" and "Kurta"
SELECT product_id, product_name, price,
       MATCH(product_name, description) AGAINST('+Lawn +Kurta' IN BOOLEAN MODE) AS relevance
FROM products
WHERE MATCH(product_name, description) AGAINST('+Lawn +Kurta' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 20;

-- Search for 2-letter products (now supported!)
SELECT product_id, product_name, price
FROM products
WHERE MATCH(product_name, description) AGAINST('+AC +Inverter' IN BOOLEAN MODE)
LIMIT 20;

Urdu Multi-Byte Search Example

Because utf8mb4_unicode_ci collates multi-byte characters accurately:

-- Search for Urdu inventory items
SELECT product_id, product_name, price
FROM products
WHERE MATCH(product_name, description) AGAINST('+کرتا +شلوار' IN BOOLEAN MODE)
LIMIT 20;

Verify index usage with EXPLAIN:

EXPLAIN SELECT product_id FROM products WHERE MATCH(product_name, description) AGAINST('Kurta' IN BOOLEAN MODE);

Expected output:

+------+-------------+----------+----------+----------------+----------------+---------+------+------+-------------+
| id   | select_type | table    | type     | possible_keys  | key            | key_len | ref  | rows | Extra       |
+------+-------------+----------+----------+----------------+----------------+---------+------+------+-------------+
|    1 | SIMPLE      | products | fulltext | idx_ft_products| idx_ft_products| 0       | NULL |    1 | Using where |
+------+-------------+----------+----------+----------------+----------------+---------+------+------+-------------+

Notice type: fulltext and key: idx_ft_products: the query executes directly against the inverted index, skipping table scans entirely!

---

High-IOPS Dedicated Database Hosting for Pakistani Commerce

Large inventory catalogs and fulltext index lookups demand substantial RAM for MariaDB’s buffer pool and lightning-fast random read speeds. In shared cloud instances, high I/O latency throttles fulltext B-tree lookups, causing search latency to spike during commercial traffic rushes.

Hosting your database infrastructure on bare-metal Dedicated Servers in Pakistan equips your platform with PCIe 4.0/5.0 NVMe drives, multi-channel ECC DDR5 memory, and dedicated AMD EPYC / Intel Xeon processors, ensuring that search queries execute in under 2 milliseconds across millions of records.