Which database indexes improve address lookup performance, and on which fields?

Which database indexes improve address lookup performance, and on which fields?

• A. Indexes on country, city, postal_code, and a normalized address_hash; a trigram or full-text index on address_line1/line2; optional spatial index on lat/lon for nearby searches.
• B. Only a primary key index on the id.
• C. No indexes are needed.
• D. Index on random columns.

Answer: (A)

The main idea is to index the parts of an address that your queries actually filter on and compare, so lookups run fast even as data grows. Indexing country, city, and postal_code helps the database quickly narrow to the relevant geographic area, and adding a normalized address_hash gives a fast way to perform exact matches or deduplication across differently formatted inputs. For the street portion, a trigram or full-text index on address_line1 and address_line2 enables efficient fuzzy or partial matching when users don’t type the full street name exactly. If you need proximity or nearby searches, a spatial index on latitude and longitude makes radius-based queries much faster. Other choices fall short because an index on only an id doesn’t accelerate address-based lookups, no indexes would slow things down, and indexing random columns generally doesn’t improve address queries. This combination provides fast, flexible address lookups across exact, fuzzy, and geographic searches.

The main idea is to index the parts of an address that your queries actually filter on and compare, so lookups run fast even as data grows. Indexing country, city, and postal_code helps the database quickly narrow to the relevant geographic area, and adding a normalized address_hash gives a fast way to perform exact matches or deduplication across differently formatted inputs. For the street portion, a trigram or full-text index on address_line1 and address_line2 enables efficient fuzzy or partial matching when users don’t type the full street name exactly. If you need proximity or nearby searches, a spatial index on latitude and longitude makes radius-based queries much faster. Other choices fall short because an index on only an id doesn’t accelerate address-based lookups, no indexes would slow things down, and indexing random columns generally doesn’t improve address queries. This combination provides fast, flexible address lookups across exact, fuzzy, and geographic searches.