Indexing - SS3 ICT Lesson Note

Index is an optimized data structure that speeds up data retrieval by allowing quick access to specific rows in a table. Indexing enhances query performance by creating a data structure that allows the database management system to quickly locate the rows that match specific query conditions. When creating indexes, consider:

Selectivity: Indexes should be selective to filter out a significant portion of the data. Highly selective indexes improve query performance.

Index Maintenance: Indexes incur overhead during data updates, so balance the benefits of improved queries with the cost of maintaining indexes.

Composite Indexes: Combine multiple columns into a composite index to support queries involving those columns.

Index Size: Large indexes consume more storage, so consider storage constraints.

Query Patterns: Design indexes based on the most common query patterns to optimize those queries.

Database Engine: Different database engines have specific index implementation options; choose the appropriate type for your use case.