By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE place_names (id SERIAL PRIMARY KEY, name VARCHAR(255));
CREATE FULLTEXT INDEX idx ON place_names (name);
INSERT INTO place_names (name) VALUES ('Munich'), ('Münster');
SELECT * FROM place_names;
| id | name |
|---|---|
| 1 | Munich |
| 2 | Münster |
SELECT name,
MATCH(name) AGAINST('+mün*' IN BOOLEAN MODE) AS relevance
FROM place_names
ORDER BY relevance DESC;
| name | relevance |
|---|---|
| Munich | 0.000000001885928302414186 |
| Münster | 0.000000001885928302414186 |
ALTER TABLE place_names ADD COLUMN name2 VARCHAR(255) COLLATE utf8mb4_0900_bin AS (name) STORED;
CREATE FULLTEXT INDEX idx2 ON place_names (name2);
SELECT name,
MATCH(name) AGAINST('+mün*' IN BOOLEAN MODE) AS relevance,
MATCH(name2) AGAINST('+mün*' IN BOOLEAN MODE) AS relevance2
FROM place_names
ORDER BY relevance DESC;
| name | relevance | relevance2 |
|---|---|---|
| Munich | 0.000000001885928302414186 | 0 |
| Münster | 0.000000001885928302414186 | 0.0906190574169159 |