The Challenge of Searching Vietnamese Text
Vietnamese uses six distinct tones applied across multiple vowel forms, producing over 60 unique accented characters. That complexity creates a concrete problem: a standard SELECT * FROM table WHERE name LIKE '%nguyen%' won’t match “Nguyễn”. The strings look related to a human eye, but to the database engine they’re completely different byte sequences.
I’ve spent six months tuning these configurations on a production system processing 200,000+ user records across MySQL and PostgreSQL. This guide documents what actually works — and what silently destroys performance at scale.
Quick Start: The 5-Minute Fix
No time for theory? Pick your database and run these commands.
MySQL: Use the Right Collation
MySQL controls accent sensitivity through Collations. Switch your column to utf8mb4_unicode_ci or the newer utf8mb4_0900_ai_ci and accent-insensitive search works without any application changes.
-- Change collation for an existing table
ALTER TABLE users
MODIFY COLUMN full_name VARCHAR(255)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;After this, searching for 'nguyen' will match 'Nguyễn' automatically.
PostgreSQL: The Unaccent Extension
PostgreSQL takes a different route. Rather than collation-level stripping, it uses an extension that removes diacritics at query time.
-- Enable the extension (requires superuser)
CREATE EXTENSION IF NOT EXISTS unaccent;
-- Test it
SELECT unaccent('Tiếng Việt có dấu');
-- Result: 'Tieng Viet co dau'Deep Dive: Vietnamese Search in MySQL
MySQL keeps things simple. The collation name suffix tells you everything: _ci means Case Insensitive, _ai means Accent Insensitive.
Why utf8mb4_0900_ai_ci?
On MySQL 8.0+, utf8mb4_0900_ai_ci is your best option. It implements the Unicode Collation Algorithm (UCA) 9.0.0. The engine correctly treats ‘a’, ‘á’, and ‘ạ’ as the same base character — for both sorting and search.
There’s a catch. LIKE '%keyword%' with any collation still triggers a full table scan. On a 500,000-row table, that means seconds per query. Use a Full-Text Index instead.
-- Add a Full-Text index
ALTER TABLE articles ADD FULLTEXT(title, content);
-- Search using Boolean Mode
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('nguyen' IN BOOLEAN MODE);Deep Dive: Vietnamese Search in PostgreSQL
PostgreSQL gives you significantly more control. The tradeoffs are also more significant. The unaccent function is powerful — used incorrectly, it becomes a performance disaster.
The Performance Trap
Never run this on tables with more than a few thousand rows:
-- DO NOT DO THIS on large tables
SELECT * FROM users
WHERE unaccent(full_name) ILIKE unaccent('%Nguyễn%');PostgreSQL evaluates unaccent() on every row before comparing. On a 200,000-row users table, this ran at 4.2 seconds in my tests. Completely unusable in a search box. The fix is a Functional Index.
Optimizing with Functional Indexes
Index the result of the function, not the raw column value. Then query using the identical expression.
-- Create an index on the unaccented data
CREATE INDEX idx_users_unaccent_name
ON users (unaccent(lower(full_name)));
-- Query using the exact same expression
SELECT * FROM users
WHERE unaccent(lower(full_name)) LIKE unaccent(lower('Nguyễn%'));That same query dropped from 4.2 seconds to under 10 milliseconds. One important detail: the index only activates when the WHERE expression matches exactly — even a minor variation bypasses it entirely.
Advanced Usage: Full-Text Search (FTS)
Single-column LIKE queries cover most use cases. For multi-column search, or when you need to rank results by relevance, you need Full-Text Search.
PostgreSQL GIN Indexes
GIN (Generalized Inverted Index) handles multi-word queries and relevance ranking efficiently. Pair it with unaccent and it covers Vietnamese diacritics too.
-- Create a search vector column (optional but recommended for speed)
ALTER TABLE articles ADD COLUMN search_vector tsvector;
-- Update it with unaccented content
UPDATE articles SET search_vector =
to_tsvector('simple', unaccent(title || ' ' || content));
-- Index it
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);The 'simple' dictionary skips stemming entirely. That’s intentional. Vietnamese grammar doesn’t stem the way English does, and applying generic stemming breaks more queries than it fixes.
Practical Tips from 6 Months in Production
1. The Storage vs. CPU Tradeoff
Computing unaccent() at query time is fine for tables under 50,000 rows. Cross that threshold and query times start climbing — around 300ms on our hardware for a sequential scan. My solution: add a dedicated name_search column that stores the pre-processed value (lowercase, no accents). Extra disk space, yes. Queries become simple index lookups with zero function calls at runtime.
2. Dealing with the ‘đ’ Character
This one caught us in production. The characters ‘đ’ and ‘Đ’ are not decomposable — they don’t have a combining diacritic, they’re their own Unicode codepoints. Older unaccent dictionaries skip them entirely. The fix in PostgreSQL is a custom translation rule:
-- Custom unaccent rules (unaccent.rules file)
'đ' 'd'
'Đ' 'D'Test this before going live. It’s easy to miss because ‘d’ and ‘đ’ look similar at a glance.
3. Handling TSearch for Vietnamese
PostgreSQL ships with dictionaries for English, French, German, and about 30 other languages. Vietnamese isn’t one of them. The simple dictionary combined with unaccent handles it reliably for most applications. External stemming libraries like vntoolkit exist, but the added complexity rarely pays off.
4. Client-Side Pre-processing
Your application code must mirror what the database index does. If the index is built on unaccent(lower(column)), the search query must apply the same transformations before hitting the database. Otherwise the index is bypassed completely. A Python example that handles ‘đ’ correctly:
import unicodedata
def normalize_search(text):
# Strip combining diacritics, then handle đ/Đ separately
nfd = unicodedata.normalize('NFD', text)
stripped = ''.join(c for c in nfd if unicodedata.category(c) != 'Mn')
return stripped.replace('đ', 'd').replace('Đ', 'D').lower()Summary Table: MySQL vs. PostgreSQL
| Feature | MySQL | PostgreSQL |
|---|---|---|
| Ease of Use | High (Collation based) | Medium (Extension based) |
| Performance | Good with FTS Index | Excellent with GIN/Functional Index |
| Flexibility | Limited | Very High |
The choice comes down to your existing stack. Already on MySQL? Upgrade to 8.0 and switch to utf8mb4_0900_ai_ci — minimal disruption, immediate results. Starting fresh or building a search-heavy application? PostgreSQL’s functional indexes and GIN search are worth the steeper setup curve for Vietnamese text.
