What indexing strategy would optimize common address lookups?

What indexing strategy would optimize common address lookups?

• A. Composite indexes on country_code, city, postal_code, and address_type; a full-text index on street fields; and a geospatial index on coordinates for radius queries.
• B. Index only the primary key; no additional indexes.
• C. Create a full-text index on all fields, including numeric codes.
• D. Index on building_number only.

Answer: (A)

Efficient address lookups depend on matching the index structure to the common query patterns: filtering by country, city, postal code, and address type; performing street-name searches; and handling proximity queries through coordinates. A composite index on country_code, city, postal_code, and address_type lets the database quickly locate records for a given country and city, optionally narrowed by postal code and type, without scanning the whole table. A full-text index on street fields enables fast, natural-language-style searching within street addresses, which regular B-tree indexes can’t do efficiently. A geospatial index on coordinates supports radius or distance queries, making “nearby addresses” lookups fast.

This combination directly targets the typical needs: precise geographic filtering, flexible street-level search, and proximity queries. It also keeps the workload balanced by not over-indexing every field or relying solely on the primary key, which wouldn’t optimize common location-based lookups.

Other approaches fall short because indexing only the primary key offers no aid for location-based filtering, a full-text index on all fields inflates storage and can hurt performance without providing meaningful benefits for numeric codes, and indexing on a single field like building_number misses the broader location and type filters that queries commonly use.