How Do Indexes Work?

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 16.  Using Indexes to Improve Performance


When an index is created, it records the location of values in a table that are associated with the column that is indexed. Entries are added to the index when new data is added to the table. When a query is executed against the database and a condition is specified on a column in the WHERE clause that is indexed, the index is first searched for the values specified in the WHERE clause. If the value is found in the index, the index returns the exact location of the searched data in the table. Figure 16.1 illustrates how an index functions.

Figure 16.1. Table access using an index.

graphics/16fig01.gif

Suppose the following query was issued:

 SELECT *  FROM TABLE_NAME WHERE NAME = 'SMITH'; 

As shown in Figure 16.1, the NAME index is referenced to resolve the location of all names equal to 'SMITH'. After the location is determined, the data can be quickly retrieved from the table. The data, in this case names , is alphabetized in the index.

A full table scan would occur if there were no index on the table and the same query was executed, which means that every row of data in the table would be read to retrieve information pertaining to all individuals with the name SMITH.


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net