Day 11: Indexes and Performance Optimization

 1. What are Indexes?
        Indexes improve query performance by allowing the database to find rows more quickly. They act as a data structure that provides a faster way to look up data.

2. Types of Indexes

    2.1. Clustered Index:
            Stores data physically in order based on indexed column(s).
            Only one per table.
            
            Example: Primary key.

            Syntax:

                CREATE CLUSTERED INDEX idx_name ON TableName(ColumnName);


    2.2. Non-Clustered Index:
            Creates a separate structure for the index while data remains unsorted.
            Multiple non-clustered indexes can exist on a table. 

             Syntax:

                  CREATE NONCLUSTERED INDEX idx_name ON TableName(ColumnName);

    2.3. Unique Index:

            Ensures all values in the indexed column(s) are unique.
            Automatically created for PRIMARY KEY and UNIQUE constraints.

            Syntax:

                CREATE UNIQUE INDEX idx_name ON TableName(ColumnName);

    2.4. Composite Index:

            Indexes multiple columns together.

            Syntax:

                CREATE INDEX idx_name ON TableName(Column1, Column2);

3. Best Practices for Indexing

Index columns frequently used in WHERE, JOIN, or ORDER BY.
Avoid over-indexing (too many indexes can slow down write operations).
Use composite indexes for multi-column searches.
Regularly update statistics for accurate query plans.

4. Query Performance Optimization

    4.1. EXPLAIN/Execution Plan:

        Use it to analyze query performance and identify bottlenecks.

        Syntax:

            EXPLAIN SELECT * FROM TableName WHERE Column = 'Value';

    4.2. Avoid SELECT :

        Only retrieve required columns to minimize data retrieval.

        Example:

            SELECT Name, Salary FROM Employees WHERE DepartmentID = 1;

    4.3. Use Joins Efficiently:

        Prefer INNER JOIN for better performance if applicable.

    4.4. Optimize WHERE Clauses:

        Use indexed columns in WHERE.

        Example:

            SELECT * FROM Employees WHERE EmployeeID = 101;

    4.5. Avoid Functions in WHERE Clauses:

        Functions prevent the use of indexes.

        Inefficient:

            SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;

        Efficient:

            SELECT * FROM Employees WHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';

    4.6. Use LIMIT/OFFSET:

        Reduce the result set size for better performance.

        Example:

            SELECT * FROM Employees LIMIT 10 OFFSET 0;

5. Dropping Unused Indexes

        Too many indexes can slow down write operations. Drop unused ones.

        Syntax:

            DROP INDEX idx_name ON TableName;

Action Steps

1. Create clustered, non-clustered, and composite indexes on a test table.
2. Use EXPLAIN or execution plans to analyze slow queries.
3. Optimize queries based on the best practices above.