SQL Foundations  «Prev  Next»
Lesson 5What is an index?
ObjectiveUse Database Table Index to speed up your Queries.

Use Database Table Index to speed up Queries

As you start working with SQL in your database, you will find that you will pay more and more attention to making your query run faster and return results more quickly. Although you will be able to accomplish some of this simply by working with the query itself (you can remove unneeded columns, decrease the number of rows that will be returned, and so on), it is also likely that an index will help.

Table Index

An index is a document that is manually created by the database. It helps the database find information more quickly by indexing locations.

To help understand what an index is about, you might want to consider your favorite file cabinet. If the folders are in the cabinet in random order, finding a folder in short order will be next to impossible.
It is more likely that you sort the folders, perhaps adding dividers to help you quickly go to the section of folders that contain the specific folder you need.
With a SQL database, the concept is the same. You will need to decide how you are going to be asking for information in the database, and then use this information to determine if it makes sense to create an index that directly supports that approach.
How to create an index will be discussed next.

SQL Queries

Indexes and SQL Tables

Question: I have often heard the term indexes discussed in relation to creating SQL tables.
How do you create indexes?
Answer: The SQL:2011 standard does not support the creation and maintenance of indexes, nor does it provide a definition or mention them in any other way. An index is a set of search values and pointers (in a subsidiary table) that correspond to rows in a table. Indexes speed up queries and improve performance, making data access much more efficient, much like using the index of a book helps you find things more quickly than sequentially searching the pages. As a result, nearly every RDBMS supports some form of indexing, and indeed they are an important part of that product. However, the method used to implement indexing varies greatly, so each product provides its own system to set up and maintain their indexes.
For example, the CREATE INDEX statement is available for most database products such as 1) MySQL, 2) Oracle, 3) DB2, 4)MS SQL Server, 5) MS Access . However, the syntax for the statement can vary considerably. As always, be sure to review the product documentation.