How to Create an Index in SQL - dummies

How to Create an Index in SQL

By Allen G. Taylor

In any SQL database, you need a quick way to access records of interest. Say, for example, that you want to look at all the proposals from people claiming to be your brother. Assuming none of your brothers have changed their last names for theatrical purposes, you can isolate these offers by basing your retrieval on the contents of the LastName field, as shown in the following SQL adhoc query:

 WHERE LastName = 'Marx' ;

That strategy may not work for the proposals made by half brothers and brothers-in-law, so you need to look at a different field, as shown in the following example:

 WHERE HowKnown = 'brother-in-law'
   HowKnown = 'half brother' ;

SQL scans the table a row at a time, looking for entries that satisfy the WHERE clause condition. If the POWER table is large (tens of thousands of records), you may end up waiting a while. You can speed things up by applying indexes to the POWER table. (An index is a table of pointers. Each row in the index points to a corresponding row in the data table.)

You can define an index for all the different ways you may want to access your data. If you add, change, or delete rows in the data table, you don’t have to re-sort the table — you need only to update the indexes. You can update an index much faster than you can sort a table.

After you establish an index with the desired ordering, you can use that index to access rows in the data table almost instantaneously.

Because the ProposalNumber field is unique and short, using that field is the quickest way to access an individual record. Those qualities make it ideal for a primary key. And because primary keys are usually the fastest way to access data, the primary key of any and every table should always be indexed; Access indexes primary keys automatically. However, you must know the ProposalNumber of the record you want.

You may want to create additional indexes based on other fields, such as LastName, PostalCode, or HowKnown. For a table that you index on LastName, after a search finds the first row containing a LastName of Marx, the search has found them all. The index keys for all the Marx rows are stored one right after another.

You can retrieve Chico, Groucho, Harpo, Zeppo, and Karl almost as fast as you could get the data on Chico alone.

Indexes add overhead to your system, which slows down operations. You must balance this slowdown against the speed you gain by accessing records through an index.

Here are some tips for picking good indexing fields:

  • Indexing the fields you frequently use to access records is always a good idea. You can speedily access records without too much latency.

  • Don’t bother creating indexes for fields that you never use as retrieval keys. Creating needless indexes is a waste of time and memory space, and you gain nothing.

  • Don’t create indexes for fields that don’t differentiate one record from a lot of others. For example, the BusinessOrCharity field merely divides the table records into two categories; it doesn’t make a good index.

The effectiveness of an index varies from one implementation to another. If you migrate a database from one platform to another, the indexes that gave the best performance on the first system may not perform the best on the new platform. In fact, the performance may be worse than if you hadn’t indexed the database at all.

Try various indexing schemes to see which one gives you the best overall performance, and optimize your indexes so that neither retrieval speed nor update speed suffer from the migration.

To create indexes for the POWER table, just select Yes for Indexed in the Field Properties pane of the table creation window.

Access does two handy tricks automatically: It creates an index for PostalCode (because that field is often used for retrievals) and it indexes the primary key. (Ah, progress. Gotta love it.)

PostalCode isn’t a primary key and isn’t necessarily unique; the opposite is true for ProposalNumber. You already created an index for LastName. Do the same for HowKnown because both are likely to be used for retrievals.

After you create all your indexes, don’t forget to save the new table structure before closing it.

If you use a RAD tool other than Microsoft Access, this info doesn’t apply to you. However, the overall process is fairly similar.