Visual FoxPro - Pros and Cons of Indexing

Question

I'm confusing what are pros and cons of that I create indexes on primary key and foreign keys of every table in a vfp database.

I generally know that if tables have indexes, we can get better performance when joining each other and filtering records.
But when I create an index on a table, VFP creates a new file CDX on disk. Thus, if I have 10 tables with primary key indexes, I will have further 10 CDX files and totally I will have 20 data files on the disk. It would take more spaces on disk. For example,

CREATE TABLE orders( ;
    odrid I AUTOINC PRIMARY KEY, ;
    cusid I, ;
    odrcode V(10) ;
)
INDEX ON cusid TAG cudid && index on FOREIGN KEY 

It would create two files orders.dbf and orders.cdx. Is it worth to create indexes on primary keys and foreign keys ? How would performance be different with indexing or without indexing ?

Answer 1

You won't have multiple CDX files - CDX stands for Compound Index. So if you create 50 index tags, they'll all be in the same CDX.

UNLESS you are specifying a separate non-structural CDX file in your INDEX ON statements. So if you are indexing MyTable.DBF and you do:

Index On field1 tag field1 Of Field1.cdx
Index On field2 tag field2 Of Field2.cdx

... will indeed create multiple CDX files. However most people just:

Index On field1 tag field1 
Index On field2 tag field2 

... which means all the indexes are in one structural CDX called MyTable.Cdx

You don't need to create indexes on any field, including primary keys, unless that field is used in a Rushmore-optimisable expression in your code. For example, SELECT-SQL, SET FILTER, COUNT and so on.

Answer 2

Definitely use indexes. In the early development of FoxPro, when you had a table with indexes, you didn't have compound indexes and had to explicitly open each table with the indexes associated, such as

use YourTable Exclusive
Index on SomeColumn to myIndex1.idx
index on AnotherColumn to myOtherIndex.idx
etc.

then, during your app, you would
use YourTable index myIndex1, myOtherIndex, etc

If you did not open all the index files and added or deleted, they would not be in synch.

When CDX (compound indexes) came into play, the index file was automatically opened by the database so they would ALWAYS stay in synch... so, the above would have translated to...

use YourTable Exclusive
Index on SomeColumn TAG SomeColumn
index on AnotherColumn TAG AnotherCol    
// Note... a maximum of 10 characters to identify the "tag" name of the index.
etc.

then, during your app, you would
use YourTable 

and all the compound index file would be opened automatically to ensure all indexes are available for query optimization.

Now, if you have 10 tables all with an "ID" column, each individual .CDX file can have its own "index on ID TAG ID" as a valid tag reference and not be in conflict with other tables index reference.

Also, since the others already stated, you can have one table indexed 20+ index combinations yet still have it all in a single .CDX file, and another table of only 2 index tags, so you'll always see the tables in "pairs" (.dbf and .cdx )... unless your table has a memo field, then you'll have the .fpt file too.

Answer 3

Please note that indices are not "free" - so do not index what you do not need to index.

Each time a file is changed (add, change, delete), the associated index records must also be addec/changed/deleted according to the index expressions - and index filter conditions.

Most often index files are significantly smaller than the original data table - but when your indices start to rise you can end up with a CDX file larger than the original DBF. While "disk space is cheap" - managing the data within that space can become more expensive - especially with indices (since the original DBF file just adds a record to the end of the table - indices must do more manipulation to keep records in order).

Indices are clearly a very important component of efficient design, but do not go overboard and index everything or it will begin to have a negative impact on performance.

Answer 4

The Index is not the Holy Grail, but is clearly more important to understand how they work, and how the Rushmore Technology uses them to improve the performance on querys:

Please, take a little time to read this:

--- Visual FoxPro and Rushmore optimization ---

--- Understanding Rushmore ---

--- Making Rushmore rush more ---

--- What is an index anyway?

TL;DR: Yes, almost all the time is better to create an index, or multiple index, or a expression index, or maybe not index at all (that's why there's a no-use-index option on data related commands on VFP), and don't worry about disk space, it's cheap nowadays.

Answer 5

Creating index tags on primary and foreign key fields of each table is standard practice in a Visual Foxpro Database. As you have seen, the typical way to do this is using the "Index On fieldName Tag Tagname" method. This will create one .CDX index file per .DBF table. Doing this ensures that your queries are optimizable. Also the .CDX index files are automatically updated when records are added, changed or deleted to your table.

So yes, it is worth creating index tags in the form of a .CDX for each table. Not creating index tags could negatively affect your queries and filtering, especially as the number of rows in your table(s) grows.

Answer 6

I once worked for a long-distance reseller, and many of our FoxPro tables were getting close to the 2 GB limit, and I was put in charge of reindexing all of the tables every night because of data corruption from our Novell server (which eventually died).

We had great performance in our application, and that was with fairly slow computers (1998-1999), primarily because of Rushmore, which was spectacular for our application. In general, we had indexes on every primary key, and every foreign key, and every piece of data with which our customer service department had to service customers -- every phone number.

One expert hint for you -- you can sometimes get incredible speed by making sure that the "ORDER" is set off before doing something that uses the Rushmore. Like "SET ORDER TO 0" (zero) and "COPY TO FOR " or "[whatever] FOR ".

FoxPro's Rushmore was so great that Microsoft purchased FoxPro, and put the technology into SQL Server, which I believe it still is, in improved form.

But... Since premature optimization is considered evil. Just add indexes as needed (when performance needs to improve). That meets your requirements up front, and gives you a chance to charge a consulting fee later, and look like a superhero at the same time.

A final idea for you. The indexes and Rushmore optimization are so great at improving performance that, if it was me, I would find a way to somehow create a RAMDISK and build my indexes on it. That would keep physical space down, and still take advantage of blazing performance. RAM is cheap, but then again, I don't know why your client is asking you to minimize disk usage.