Fix: Database Encoding Breaks Search Results

You've got a database-driven site (WordPress, Django, Laravel — doesn't matter which). Search worked fine yesterday. Then someone imported a database dump, or you migrated to a new server. Now search returns zero results. Or it returns garbage characters. Or it just hangs.

This exact scenario: a user types "café" in the search box. The database stores it as "café" because the connection encoding didn't match the table encoding. The search query looks for the real UTF-8 bytes, finds nothing, and returns empty. I've seen this happen after a mysqldump that didn't set --default-character-set=utf8mb4, or when a PHP script connected with SET NAMES latin1.

Why This Happens

The root cause: your database tables are in one encoding (say, latin1 or utf8mb3), but the application expects utf8mb4. Or the connection between your app and the database uses a different encoding than the stored data. Search algorithms compare bytes. If the bytes don't match, no result.

Most modern apps expect utf8mb4 (full Unicode with emoji support). Old imports often bring latin1 or utf8 (which is actually utf8mb3 in MySQL, limited to 3-byte characters). That mismatch kills search.

How to Fix It

The real fix: change the database and all tables to utf8mb4 with utf8mb4_unicode_ci collation. Don't skip steps. Here's how.

1. Backup your database first. If you mess this up, you need a rollback. Run:
   

   mysqldump -u root -p --all-databases --default-character-set=utf8mb4 > backup.sql

2. Check current encoding of the database and the table that holds search data. Connect to MySQL and run:
   

   SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
     FROM information_schema.SCHEMATA
     WHERE SCHEMA_NAME = 'your_database_name';

   
   Also check the table:
   

   SHOW CREATE TABLE your_table_name;

   
   Look for ENGINE=InnoDB DEFAULT CHARSET=latin1. If you see latin1, that's your problem.
3. Change the database default encoding (this sets the default for new tables, but existing tables stay the same):
   

   ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

   
   After running, you should see: Query OK, 1 row affected. It's quick.
4. Convert each table that stores searchable content. Run this for the main table (replace with your actual table name):
   

   ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

   
   This changes the table encoding and converts the data. It may lock the table for a few seconds if it's large. Do it during low traffic.
5. Fix the application's database connection encoding. In your app config file (e.g., wp-config.php, .env, database.yml), set the charset to utf8mb4 and collation to utf8mb4_unicode_ci. For a PHP PDO connection, example:
   

   $pdo = new PDO('mysql:host=localhost;dbname=your_database_name;charset=utf8mb4', $user, $pass);

   
   If you use MySQLi, add:
   

   $mysqli->set_charset('utf8mb4');

   
   After changing, restart your web server or reload the app config.
6. Test search with a phrase that has special characters (like "café" or "São Paulo"). If it works now, you're done. If not, go to the next step.

If It Still Fails

Two more things to check.

1. Check the search query itself. Some apps build search queries with hardcoded collation. In MySQL, you can force collation in a query like this:

SELECT * FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%café%';

2. Rebuild the fulltext index if you use MySQL's fulltext search. After changing encoding, the index might be stale. Drop and recreate it:

ALTER TABLE your_table_name DROP INDEX your_fulltext_index_name;
ALTER TABLE your_table_name ADD FULLTEXT INDEX your_fulltext_index_name (column1, column2) WITH PARSER ngram;

3. Verify that the application's search function isn't caching old results. Clear any cache — Redis, Memcached, or app-level cache. Then test again.

One last thing: if you migrated from a server with MySQL 5.x to 8.x, the default collation changed. MySQL 8 uses utf8mb4_0900_ai_ci by default. Your old tables might still be utf8mb4_general_ci. That works fine for search, but it's worth knowing. I'd stick with utf8mb4_unicode_ci for broad compatibility.

That's it. Encoding problems are boring but they're easy to fix once you know where to look.