Enhancing the Efficiency of Indexes

  • Article

You can improve the performance of indexed tables by keeping indexes current and using optimizable expressions in your indexes.

Rebuilding an Active Index File

Index files become outdated when you open a table without opening its corresponding index files and make changes to the key fields in the table. Index files can also become invalid as a result of a system crash, or potentially by accessing and updating a table from a program other than Visual FoxPro. When index files become outdated, you can update them by re-indexing with the REINDEX command.

To rebuild an active index file

  • From the Table menu, choose Rebuild Indexes.

    -or-

  • Use the REINDEX command.

For example, the following code updates the index file for the Customer table:

USE customer
REINDEX

REINDEX updates all index files open in the selected work area. Visual FoxPro recognizes each index file type (compound index .cdx files, structural .cdx files, and single index .idx files) and re-indexes accordingly. It updates all tags in .cdx files, and updates structural .cdx files, which open automatically with the table.

You can also update outdated index files with the REINDEX command.

Re-Indexing at Run Time

Re-indexing takes time, particularly when you're re-indexing large tables. You should re-index only when necessary. You can enhance performance by re-indexing during the initialization or termination portion of your program, rather than performing indexing maintenance during the main portion of an application.

Using Indexes to Optimize Queries

You can use indexes to speed queries and other operations. For information on creating Rushmore-optimizable index expressions, see Optimizing Applications.

See Also

Filtering Data | Working with Multiple Tables | Working with Records | Working with Table Aliases | Creating One Index | Creating Multiple Indexes | Ordering by Multiple Fields | Index Creation for Tables