SQL advanced -> Using Indexes to improve queries performance

Creating indexes is one of the famous ways to improve query performance. Index can be viewed as a table that stores Key values in a sorted manner (like B-Tree structure) and a pointer to physical location of the record in the memory. SQL engine knows how to implement very fast searching algorithms using indexes. Making good index is a skill that can be acquired only by a lot of learning and experience. I'll mention few basic facts about index creation.

1. Choosing candidate column for index
It is nice to put on index on the column when:

  • this is column with unique identity

  • column of numeric data type

  • column that is frequently used in the WHERE, ORDER BY, GROUP BY, and DISTICT clauses

  • column that is frequently used in the JOINs between tables

    • In "Colombo" case CustomerID column is a natural candidate for index in two tables: Customers and Invoice.

    2. When it is bad to have indexes ?
    if your database is used by OLTP applications (those that produce high volume of "Inserts/Updates/Deletes") their performance can be negatively affected by indexes. Then you will need to calculate carefully the costs of Having index in such environment. You also usually do not need indexes when loading large amounts of Data from external sources. That mean: Sometime it is better to have less indexes or not to have them at all. For your convenience, you can DISABLE indexes for particular periods and ENABLE them for the time when you really need them.
    MS-Access have Defualt AutoIndexing property on Create Table Or Import data operations. It is usually set to the following string: "ID;key;code;". That means that every column with name like "code" will be Automatically indexed by Access. You can go to: "Tools -> Options -> Table/Queries" and change this property.

    3. Monitoring Index health
    Indexes are tends to go out of shape after intensive use in OLTP environment. From time to time you will need to check and fix them.
    On SQLsever you have useful commands that helps to monitor index status, like:
    /* system stored procedure sp_updatestats refreshes database statistics and helps SQL engine make more efficient decisions about query execution */

    EXEC sp_updatestats

    /* DBCC SHOWCONTIG command is very useful to monitor physical storage conditions of particular table and it's indexes */

    DBCC SHOWCONTIG (customers) WITH ALL_INDEXES

    /* to recreate indexes on Customers table run: */

    DBCC DBREINDEX (Customers)

    In desktop DBMS like Access there are no build-in tools for monitoring Index health. Then you can do following:
    /* suppose that there is index on CustomerName column */
    CREATE UNIQUE INDEX idx1_CustNm
    ON Customers (CustomerName)
    /* drop index */
    DROP INDEX idx1_CustNm ON Customers
    /* Goto database file Menu: Tools -> Database Utilities -> Compact And Repair Database… then create index again: */
    CREATE UNIQUE INDEX idx1_CustNm
    ON Customers (CustomerName)


    4. Learn from experts:
    There is no single web tutorial or book that can cover every aspect of index creation and tuning. But there are few good ones:
    Database Tuning: Principles, Experiments, and Troubleshooting Techniques



    sqlexamples.info