Indexing Strategy
Also known as: index design, indexing
Indexing strategy is the practice of systematically designing database indexes to accelerate query performance. Developed following Bayer and McCreight's foundational B-tree work in 1972, effective indexing requires analyzing query patterns, choosing appropriate index structures, and maintaining index health as data evolves.
Key highlights
- Can reduce query execution time from seconds to milliseconds for selective predicates
- Applies to existing applications without code modification
- Adapts to changing query patterns through index redesign and addition
Intuition
This section is available to Pro members. Upgrade to Pro
How it works
This section is available to Pro members. Upgrade to Pro
When to use it
Use indexing strategy whenever query response time is unacceptable or resources are over-utilized. It is most effective for columns used in WHERE, JOIN, and ORDER BY clauses, and for tables with selective queries. Assumptions include that update performance trade-offs are acceptable and that statistics are current.
Strengths & limitations
- Can reduce query execution time from seconds to milliseconds for selective predicates
- Applies to existing applications without code modification
- Adapts to changing query patterns through index redesign and addition
- Indexes consume storage and slow down INSERT, UPDATE, and DELETE operations
- Index maintenance overhead can become significant for high-write workloads
- Poorly chosen indexes provide no benefit and waste resources
Common pitfalls
This section is available to Pro members. Upgrade to Pro
Applications
This section is available to Pro members. Upgrade to Pro
Frequently asked
Should I index every column used in a WHERE clause?
No. Prioritize columns with high selectivity (that eliminate many rows) and columns used frequently. Low-selectivity columns (e.g., gender with 2 values) provide minimal benefit. Use composite indexes for multiple frequently-used columns.
What is a composite index and when should I use it?
A composite index includes multiple columns in a specific order. Use when queries frequently filter or sort by multiple columns together. Example: INDEX(customer_id, order_date) helps queries that filter by customer AND order date.
How do I know if an index is being used?
Use EXPLAIN or EXPLAIN PLAN to show the query execution plan. If the plan shows an index name and Index Scan/Seek operations, the index is being used. If it shows Table Scan, the index is not being used.
What is index fragmentation and why does it matter?
Fragmentation occurs when index pages are not stored contiguously on disk, requiring more I/O operations. Defragmentation (REBUILD or REORGANIZE) restores contiguity and improves performance, especially for range queries.
Sources
- 1.Bayer, R., & McCreight, E. (1972). Organization and maintenance of large ordered indices. Acta Informatica, 1(3), 173-189.
- 2.Seltzer, M., & Bostic, K. (1994). An implementation of a log-structured file system for UNIX. Winter USENIX Conference, 307-326.
- 3.Garcia-Molina, H., Ullman, J. D., & Widom, J. (2009). Database Systems: The Complete Book (2nd ed.). Pearson Education.
You have read it. What now?
Cite this page
ScholarGate. (2026, June 3). Indexing Strategy. ScholarGate. https://scholargate.app/information-systems/indexing-strategy