|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:
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:
In desktop DBMS like Access there are no build-in tools for monitoring Index health. Then you can do following:
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