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)

Indexing strategy should match how you query addresses. For common address lookups, you want speed where filters and searches happen most often. A composite index on country_code, city, postal_code, and address_type lets the database quickly narrow results when these fields are used together in the WHERE clause, which is a typical pattern for locating an address. A full-text index on street-related fields makes street-name searches fast, outperforming broad wildcards. A geospatial index on coordinates supports radius queries, enabling fast results when you need addresses within a certain distance of a point. Relying only on the primary key doesn’t help with common multi-field location lookups, and a full-text index on all fields (including numeric codes) would be wasteful and unlikely to improve numeric lookups. Indexing just building_number would miss the majority of typical address lookups that involve location, street names, or proximity.

Indexing strategy should match how you query addresses. For common address lookups, you want speed where filters and searches happen most often. A composite index on country_code, city, postal_code, and address_type lets the database quickly narrow results when these fields are used together in the WHERE clause, which is a typical pattern for locating an address. A full-text index on street-related fields makes street-name searches fast, outperforming broad wildcards. A geospatial index on coordinates supports radius queries, enabling fast results when you need addresses within a certain distance of a point.

Relying only on the primary key doesn’t help with common multi-field location lookups, and a full-text index on all fields (including numeric codes) would be wasteful and unlikely to improve numeric lookups. Indexing just building_number would miss the majority of typical address lookups that involve location, street names, or proximity.