The Nightmare of “Vietnamese Search”
When building applications for Vietnamese users, the “type unaccented, get accented results” requirement is a must-have, not just an extra feature. Customers expect searching for ‘dien thoai’ to still return ‘điện thoại’. If you only use LIKE %keyword%, your system will soon gasp for air as data grows.
I once managed a production database running MySQL 8.0 with over 10 million records (roughly 50GB). Initially, search queries took 2-3 seconds to respond, pushing server CPU to an alarming 80-90%. After applying the optimization techniques below, query time dropped to under 150ms, with CPU usage stabilizing at 10%.
Evaluating Common Methods
1. Using LIKE and Collation (The Manual Way)
This is the “instant noodle” solution. You typically choose the utf8mb4_unicode_ci or utf8mb4_vietnamese_ci collation. However, MySQL cannot automatically treat ‘d’ and ‘đ’ as the same without extremely complex configuration.
- Weakness: Causes Full Table Scans. The database must scan every single row, which is incredibly resource-intensive.
- Reality: Should only be used for category tables with fewer than 1,000 rows.
2. Default MySQL Full-Text Search (FTS)
FTS is many times faster than LIKE thanks to its specialized indexing mechanism. However, it still struggles with specific Vietnamese accented characters if left at default settings.
3. Shadow Column Technique – The Performance King
This is my go-to technique for large projects. The idea is simple: We create an auxiliary column (e.g., search_vector) to store content with accents removed. When a user searches, we simply scan this “clean” column.
Implementing Shadow Columns with Full-Text Search
Step 1: Restructuring the Data Table
Add a dedicated column to store unaccented text. Don’t forget to create a Full-Text Index for this column immediately.
ALTER TABLE products ADD COLUMN content_search_no_sign TEXT;
CREATE FULLTEXT INDEX idx_fts_vietnamese ON products(content_search_no_sign);Step 2: Normalizing Data at the Application Layer
Many developers write functions directly in MySQL to remove accents. However, this inadvertently forces the Database Server to handle additional computational logic. Move this task to the Backend (NodeJS, Python, PHP).
For example, with NodeJS, you can use the unidecode library to convert “Tiếng Việt có dấu” to “Tieng Viet co dau” before saving it to the DB. This approach minimizes the load on the database.
Step 3: Automating with Triggers
To ensure data in the main and auxiliary columns always match, Triggers are an excellent choice. Every time the title is updated, the content_search_no_sign column will automatically update as well.
CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
-- Call the predefined accent removal function
SET NEW.content_search_no_sign = remove_accents(NEW.title);
END;Configuring MySQL for Better Vietnamese Support
Vietnamese is characterized by many short words like “áo” (shirt), “xe” (car), “tủ” (cabinet). By default, MySQL ignores words shorter than 3 characters, causing search results to be severely incomplete.
Adjusting the Minimum Word Length
You need to modify the my.cnf (Linux) or my.ini (Windows) configuration file:
[mysqld]
innodb_ft_min_token_size = 2
ft_min_word_len = 2After editing, restart the service and run the command OPTIMIZE TABLE products;. This step is crucial for MySQL to rebuild the index based on the new rules.
Harnessing the Power of Boolean Mode
Don’t just use standard searches. Leverage IN BOOLEAN MODE for more precise result filtering.
-- Search for products that must contain the words "dien" and "thoai"
SELECT * FROM products
WHERE MATCH(content_search_no_sign) AGAINST('+dien +thoai' IN BOOLEAN MODE);Pro Tips from Real-World Experience
When a system reaches millions of users, storing only the unaccented version sometimes reduces accuracy. A trick I often use is to store both versions in the same column, separated by a space. For example: "điện thoại dien thoai".
Now, regardless of how the user types, MySQL will find it. Specifically, results that match the accented version will have a higher relevance score and appear at the top. This is how large systems optimize user experience.
Conclusion
If your project is small-scale, LIKE is still fine. But for long-term and professional growth, remember the three pillars: Shadow Columns, Backend accent processing, and reconfiguring min_token_size. Happy querying!
