Full-Text Location Search with MySQL 8.4

A location field should accept whatever part of a place a person remembers: a postal code, locality, region, country, or a useful combination such as 80331 München Bayern. This is a text-retrieval problem before it is a geometric one. Coordinates become relevant after a place has been identified, for example when finding locations within a radius.

MySQL 8.4 can provide a compact location search without an external search service. The central design choice is to materialize one search document per locality. A FULLTEXT index cannot span joined tables, and indexing each postal code as a separate document would split the evidence that belongs to the same place.

Model the Location Hierarchy

The source tables retain the normalized relationships. Regions cover states, provinces, cantons, and similar administrative areas; aliases hold alternate or multilingual locality names.

CREATE TABLE region (
  region_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  country_code CHAR(2) NOT NULL,
  country_name VARCHAR(96) NOT NULL,
  region_code VARCHAR(16) NOT NULL,
  name VARCHAR(128) NOT NULL,
  UNIQUE KEY uq_region (country_code, region_code)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE locality (
  locality_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  region_id INT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  population BIGINT UNSIGNED NULL,
  location POINT NOT NULL SRID 4326,
  KEY ix_locality_region (region_id),
  CONSTRAINT fk_locality_region
    FOREIGN KEY (region_id) REFERENCES region (region_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE locality_alias (
  locality_id BIGINT UNSIGNED NOT NULL,
  alias VARCHAR(160) NOT NULL,
  PRIMARY KEY (locality_id, alias),
  CONSTRAINT fk_alias_locality
    FOREIGN KEY (locality_id) REFERENCES locality (locality_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE postal_code (
  locality_id BIGINT UNSIGNED NOT NULL,
  postal_code VARCHAR(16) NOT NULL,
  PRIMARY KEY (locality_id, postal_code),
  KEY ix_postal_code (postal_code),
  CONSTRAINT fk_postal_locality
    FOREIGN KEY (locality_id) REFERENCES locality (locality_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

utf8mb4_0900_ai_ci compares text case-insensitively and accent-insensitively. That makes Munchen useful for finding München, while the stored spelling remains suitable for display. Whether that behavior is appropriate depends on the languages in the data. Search aliases are still necessary for historical names, exonyms, abbreviations, and genuinely different spellings.

The WGS 84 point is not part of the text index. It belongs in the normalized locality row, where a spatial query can use it after text search has identified candidate locality IDs.

Create One Search Document per Locality

The derived table contains values needed to retrieve and rank candidates. Keeping it independent of the source tables also permits an atomic rebuild.

CREATE TABLE location_search (
  locality_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  primary_name VARCHAR(160) NOT NULL,
  display_name VARCHAR(384) NOT NULL,
  search_text TEXT NOT NULL,
  population BIGINT UNSIGNED NULL,
  FULLTEXT KEY ft_location_search (search_text)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

Populate it from pre-aggregated aliases and postal codes. Pre-aggregation avoids the aliases-by-postal-codes Cartesian product that a direct multi-table aggregate would create:

INSERT INTO location_search (
  locality_id,
  primary_name,
  display_name,
  search_text,
  population
)
WITH
alias_text AS (
  SELECT
    locality_id,
    GROUP_CONCAT(DISTINCT alias ORDER BY alias SEPARATOR ' ') AS aliases
  FROM locality_alias
  GROUP BY locality_id
),
postal_text AS (
  SELECT
    locality_id,
    GROUP_CONCAT(DISTINCT postal_code ORDER BY postal_code SEPARATOR ' ') AS postal_codes
  FROM postal_code
  GROUP BY locality_id
)
SELECT
  l.locality_id,
  l.name,
  CONCAT_WS(', ', l.name, r.name, r.country_name),
  CONCAT_WS(
    ' ',
    l.name,
    a.aliases,
    r.name,
    r.region_code,
    r.country_name,
    r.country_code,
    p.postal_codes
  ),
  l.population
FROM locality AS l
JOIN region AS r ON r.region_id = l.region_id
LEFT JOIN alias_text AS a ON a.locality_id = l.locality_id
LEFT JOIN postal_text AS p ON p.locality_id = l.locality_id;

GROUP_CONCAT(DISTINCT ...) writes each alias and postal code once. Repeating terms changes term frequency and therefore changes relevance for reasons unrelated to user intent. Before importing unusually large locality records, verify group_concat_max_len; silent truncation would make part of a place unsearchable.

Turn User Input into a Boolean Prefix Query

Boolean mode supplies the two behaviors needed for incremental location input. A leading + requires a term, and a trailing * matches indexed words beginning with that prefix. The input 80331 München Bayern therefore becomes:

+80331* +München* +Bayern*

Do not pass raw input into MySQL's Boolean query language. Characters such as +, -, parentheses, quotes, and @ are operators. Extract words in application code, add the operators yourself, and bind the resulting string as a parameter. This PHP function accepts letters and numbers, removes duplicates, limits work per request, and rejects an empty query:

<?php

declare(strict_types=1);

function locationPrefixQuery(string $input): string
{
  preg_match_all('/[\p{L}\p{N}]+/u', $input, $matches);
    $tokens = array_slice(array_values(array_unique($matches[0])), 0, 8);

    if ($tokens === []) {
        throw new InvalidArgumentException('Enter a locality or postal code.');
    }

    return implode(
        ' ',
        array_map(static fn (string $token): string => '+' . $token . '*', $tokens)
    );
}

The wildcard keeps a prefix in the Boolean query even when it is shorter than innodb_ft_min_token_size or appears in the stopword list. A one-character query is technically possible but usually too broad for an interactive endpoint, so the HTTP layer should require a practical minimum input length and apply rate limits.

Search and Rank Candidates

Use the same bound Boolean string for both MATCH() expressions. The second parameter contains the user's complete trimmed input and provides an exact-primary-name boost under the table collation.

WITH candidates AS (
  SELECT
    locality_id,
    primary_name,
    display_name,
    population,
    MATCH(search_text) AGAINST (? IN BOOLEAN MODE) AS text_score,
    CASE WHEN primary_name = ? THEN 1 ELSE 0 END AS exact_name
  FROM location_search
  WHERE MATCH(search_text) AGAINST (? IN BOOLEAN MODE)
)
SELECT
  locality_id,
  display_name,
  text_score,
  exact_name,
  text_score * (
    1 + 0.12 * LN(1 + COALESCE(population, 0))
  ) AS score
FROM candidates
ORDER BY exact_name DESC, score DESC, display_name
LIMIT 20;

Boolean full-text search does not automatically sort by relevance, so the explicit ORDER BY is part of the query contract. The logarithm makes population a tie-breaking prior rather than letting a metropolis overwhelm a substantially better textual match. The coefficient 0.12 is a product decision and should be measured against real queries.

Use actual population from a documented dataset. The number of postal codes is not a population estimate: postal boundaries reflect delivery operations, can represent institutions or post-office boxes, and differ substantially between countries. If population is unavailable, leave it NULL and rank from text plus explicit business rules.

Understand the Relevance Score

InnoDB Boolean relevance is based on term frequency and inverse document frequency. A rare postal code contributes more than a region name present in thousands of rows, which is generally useful. It also means the score is meaningful only within the current index and query. Do not expose it as a stable probability.

One row per locality is important here. If each postal code were a separate row, large cities would produce many competing documents and duplicate suggestions. If all postal codes are repeated alongside every alias, term frequency can dominate the intended hierarchy. The materialized document keeps the retrieval unit aligned with the result shown to the user.

Refresh the Index

For a small data set, truncate and repopulate location_search inside a maintenance window. For a complete production import, build location_search_next without its FULLTEXT index, load and validate the rows, add the index, then swap tables with one RENAME TABLE statement. MySQL documents that bulk loading before creating the full-text index is faster for large data sets.

Incremental updates can rebuild one locality document with the same aggregation and INSERT ... ON DUPLICATE KEY UPDATE. Treat changes to aliases, postal codes, regions, and population as changes to that document. A periodic comparison of source locality IDs against indexed IDs catches missed events.

Test Search Quality and Performance

A useful relevance suite contains exact names, prefixes, accent variants, aliases, region-only searches, postal codes, duplicate names in different regions, and intentionally absent values. Record the expected first few IDs, not floating-point score literals, because IDF changes as the collection changes.

EXPLAIN ANALYZE
SELECT locality_id, display_name
FROM location_search
WHERE MATCH(search_text) AGAINST ('+80331* +münchen*' IN BOOLEAN MODE)
LIMIT 20;

Confirm that the plan uses the full-text access path and test with production-scale data. Prefixes with one or two characters can match a large fraction of the index even when an index is used. Debounce autocomplete requests, cap the number of tokens and results, and cache common normalized queries.

Know When FULLTEXT Is Not Enough

The built-in parser provides word and prefix retrieval, not typo tolerance, phonetic matching, street-address parsing, or language-aware stemming for place names. Chinese, Japanese, and Korean text requires MySQL's ngram parser or another appropriate tokenizer. A dedicated search engine becomes worthwhile when the product needs fuzzy edits, per-language analyzers, complex synonyms, or relevance tuning across many fields.

Text search also does not answer distance questions. After resolving a locality ID, use the stored SRID 4326 point with a spatial candidate filter and an exact distance calculation. Keeping name resolution and geometry as separate stages makes both indexes selective and both ranking models understandable.

References

• [MySQL-FTS]MySQL 8.4 Reference Manual: Full-Text Search Functions.
• [MySQL-Boolean]MySQL 8.4 Reference Manual: Boolean Full-Text Searches.
• [MySQL-Stopwords]MySQL 8.4 Reference Manual: Full-Text Stopwords.
• [MySQL-InnoDB-FTS]MySQL 8.4 Reference Manual: InnoDB Full-Text Indexes.