Database Index Types

 Referred Link - https://www.linkedin.com/posts/curiouslearner_databases-softwaredevelopment-activity-7218788954823680000-ylVF

Database Index Types 

Pro Tip from Author -> Don't Index Everything.

More indexes ≠ better performance.

Each index adds overhead for data modifications (inserts, updates, deletes).

Focus on columns frequently used in WHERE, JOIN, ORDER BY or GROUP BY clauses.

[1.] Clustered Index
◾ Physically reorders data rows based on the index key.
◾ One per table.
◾ Significantly speeds up range queries (e.g., find all orders between date X and Y) and queries on the indexed column(s).
◾ Can slow down data modifications (inserts, updates, deletes) due to reordering.

[2.] Non-Clustered Index
◾ Creates a separate structure with pointers to data rows, sorted by the index key.
◾ Multiple allowed per table.
◾ Efficient for equality searches (e.g., find the customer with ID 123).
◾ Can be used for covering indexes, where all the required columns are included in the index.

[3.] Unique Index
◾ Ensures that all values in a column or a combination of columns are unique.
◾ Can be clustered or non-clustered.
◾ Prevents duplicate values, ensuring data integrity.
◾ Often used on alternate keys (other than the primary key) that must be unique.

[4.] Filtered Index
◾ Indexes only a subset of rows based on a filter predicate.
◾ Improves performance for queries that frequently filter on the specified condition.
◾ Reduces index size compared to indexing the entire table.

[5.] Full-Text Index
◾ Enables efficient searching within text data (e.g., documents, articles) based on keywords and phrases.
◾ Uses specialized indexing and search algorithms (e.g., inverted indexes).
Supports linguistic features like stemming and thesaurus.

[6.] Composite Index
◾ An index created on multiple columns.
◾ Can be clustered or non-clustered.
◾ Optimizes queries that filter or sort on multiple columns.
◾ The order of columns in the index definition is crucial for performance.

[7.] Covering Index
◾ Includes all columns needed for a query in the index itself, avoiding the need to access the base table.
◾ Can be non-clustered.
◾ Significantly speeds up queries that only need data from the indexed columns.

[8.] Index with Included Columns
◾ Similar to a covering index, but only includes non-key columns.
◾ Non-clustered.
◾ Can improve query performance if the additional columns are frequently used.

[9.] XML Index
◾ Indexes XML data for efficient querying using XPath or XML-specific functions.
◾ Available in databases that support XML data types.
◾ Enables querying specific elements or attributes within XML documents.

[10.] Partial Index
◾ Indexes only a subset of rows that meet a specific condition.
◾ Similar to filtered indexes but with more flexibility in defining filter conditions.
◾ Can improve query performance for niche use cases.

[11.] Bitmap Index
◾ Efficiently indexes columns with a low cardinality (few unique values).
◾ Stores bitmaps indicating which rows contain a particular value.
◾ Compact and fast for certain types of queries.

Tags:

#DatabaseIndexTypes, #PerformanceTips, #Indexing