XL: How to Create an Index with Microsoft Query

ID: Q143348

The information in this article applies to:

SUMMARY

This article describes how to create indexes with Microsoft Query.

MORE INFORMATION

Indexes can make queries run more quickly. To improve query performance, you can create indexes for fields that are used in joins, criteria, and sorts.

The more unique a field's entries are, the more an index for that field can improve query performance. For example, an index for a field of phone numbers will improve query performance more than an index for a field containing only Yes or No entries.

To create an index with Microsoft Query, follow these steps: 

 1. On the File menu, click Table Definition.

 2. In the Select Data Source dialog box, select a data source and then
    click Use (in versions 5.0 and 7.0) or click OK (in the 97 version).

 3. In the Select Table dialog box, select a table and then click Index.

 4. In the Create Index dialog box, under Index Name, type a name for
    the index.

 5. Select the field to be indexed.

   Depending on the ODBC driver you are using, you may be able to index on
   multiple fields, or index on an expression. For example, your ODBC
   driver may allow you to enter either of the following:

       LAST_NAME,FIRST_NAME
       RATE*INTEREST

 6. Ensure that the Unique Index box is selected, and then click Add.
    Note that you receive the following message:

       Index was created successfully.

    If you receive an error message, click to clear the Unique Index
    check box.

 7. Click OK.

 8. Repeat steps 5-7 for any other fields that you want to be
    indexed on that table.

 9. Click Close.
10. Repeat steps 3-9 for any other tables with fields that you 
    want to be indexed.
When you run your query again, you may receive better performance.

Note that indexes can make data entry slower because the indexes are updated each time data is entered. If you find that your indexes are slowing down the data entry process, delete indexes for fields which contain few unique entries. For example, if a field of phone numbers is used in a criteria, create an index for it because there are many unique entries. But if 10 fields, each containing only a Yes or a No entry, are used in criteria, you may prefer not to create indexes for these fields because they will do little to improve query performance and may slow down data entry.

REFERENCES

For more information about creating indexes in Microsoft Query, click the Search button in Microsoft Query Help and type: 

   Create Index Dialog Box
Additional query words: idx indexing fast slow long time msquery 
Keywords          : kbdta xlquery 
Version           : WINDOWS:5.0,7.0,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: January 7, 1999