Database Essentials - Looking Up The Index
(Page 5 of 9 )
If your table contains a large number of records, you can often speed up queries by "indexing" certain columns. Similar in concept to the index you find at the end of a book, a database index is a list of sorted field values, used to simplify the task of locating specific records in response to queries.
Typically, it's a good idea to index fields on which you carry out a large number of searches. For example, since most of the queries on the tables above take place via the CustomerID field, it's a good idea to index this field. Normally, if you had a query like this,
sql> SELECT * FROM names WHERE CustomerID = 30000;
the database server would have to scan each and every record in the
table to locate the one with CustomerID 30000. By indexing this field, like this,
sql> CREATE INDEX ids ON names (CustomerID);
the database server can use the index to locate ID 30000, and then use
the information stored in the index to quickly locate the relevant record on the disk drive.
The downside of using indices is that they usually occupy some amount of disk space, and can affect the time taken to alter (insert or update) records, since the index needs to be refreshed each time the data changes.
Next: Joined At The Hip >>
More Administration Articles
More By icarus, (c) Melonfire
/ 7 
