Indexing
Indexing is a database optimization technique that improves the speed of data retrieval operations by creating a data structure (an index) that allows for faster lookup of records. It works similarly to a book's index, providing pointers to data locations without scanning the entire dataset. Common types include B-tree, hash, and full-text indexes, each suited for different query patterns.
Developers should use indexing when dealing with large datasets where query performance is critical, such as in high-traffic web applications or analytical systems. It's essential for optimizing SELECT queries with WHERE, JOIN, or ORDER BY clauses, but requires careful management to balance read speed with write overhead (since indexes must be updated on data modifications).