What combination of indexing should be used to support multi-criteria queries while controlling maintenance costs?

• A. Primary keys, selective secondary indexes, composite indexes; monitor maintenance costs.
• B. Create a global index on every column.
• C. Rely solely on full table scans.
• D. Use only primary keys and ignore secondary indexes.

Answer: (A)

Balancing query speed for multi-criteria searches with the cost of maintaining indexes means choosing a focused set of indexes that support common query patterns without overwhelming the system with write overhead. Using primary keys provides fast, unique-access paths to rows. Adding selective secondary indexes targets fields you frequently filter on, delivering quick lookups where they matter while avoiding the upkeep burden of indexing every column. Complementing these with composite indexes, which cover common combinations of fields, lets multi-criteria queries be satisfied efficiently by a single index lookup rather than combining multiple scans. The key is to monitor maintenance costs and adjust which indexes exist as workload evolves, so you keep fast queries for the right patterns without excessive write-time or storage overhead.

Creating a global index on every column would explode maintenance and storage and slow down writes. Relying solely on full table scans trades speed for simplicity, which becomes impractical as data grows. Using only primary keys and ignoring secondary indexes would miss many useful query paths, leading to slower multi-criteria filtering.