An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space.

Most SQL databases use B-Tree structures for indexing.

• It keeps data sorted and allows for binary search.
• When you search for a value, the DB traverses the tree instead of scanning the whole table (Full Table Scan).

• Clustered Index: Determines the physical order of data in the table. Each table can have only one (usually the Primary Key).
• Non-Clustered Index: Stores a sorted list of values and pointers to the actual data rows.
• Unique Index: Ensures that indexed columns do not have duplicate values.
• Composite Index: An index on multiple columns.

• Index the Right Columns: Search columns (WHERE), Join columns (ON), and Sorting columns (ORDER BY).
• Avoid Over-indexing: Every index slows down INSERT, UPDATE, and DELETE operations.
• Selectivity: High selectivity (columns with many unique values) makes for better indexes.

sql Standard
-- Creating an index
CREATE INDEX idx_user_email ON Users(Email);

-- Composite index
CREATE INDEX idx_order_date_status ON Orders(OrderDate, Status);

• Indexing Boolean columns: Creating an index on a bit or boolean column (low selectivity; rarely used by DB optimizer).
• Wildcard searching: Using LIKE '%keyword' (leading wildcard). This prevents the DB from using the index.
• Functions on Columns: Using functions on indexed columns in WHERE clause (e.g., WHERE YEAR(JoinDate) = 2023). This disables index usage (SARGability).