Indexes speed up reads by building fast lookup structures, usually B-trees, on specific columns. Without indexes, the database may perform full table scans that inspect every row. Index Types Primary keys and unique constraints often create indexes automatically, but secondary indexes are still needed for frequently filtered, joined, or sorted columns. Common types include single-column indexes, unique indexes, composite indexes for multi-column predicates, and clustered indexes in engines that support them. Query Optimization Avoid SELECT , limit unnecessary retrieval, filter early in WHERE clauses, avoid wrapping indexed columns in functions, and replace expensive correlated subqueries with joins or CTEs. EXISTS is often more efficient than IN for large subqueries. Execution Plans Use…
Key Takeaways
Indexes speed up reads but add write overhead on inserts, updates, and deletes.
Index columns commonly used in WHERE, JOIN, and ORDER BY clauses.
Prefer sargable predicates over functions applied to indexed columns.
Read execution plans before and after tuning changes.