2007-08Aug-20

Kill your index with REINDEX

When you read about repairing indexes in FoxPro forums, you frequently get the advice to avoid REINDEX. The most common reasoning is that REINDEX depends on the header which might as well be corrupt. That's true, but like most developers, I haven't seen a corrupt index header in years. The header is only updated when you add a tag. As this requires exclusive access to the table, there's little chance of introducing corruption due to caching, multi-user issues, and the like.

REINDEX is a bad idea, nonetheless. In order to create a brand new CDX file, Visual FoxPro has to move the old CDX file out of the way. For a short moment the table exists without a CDX file. A moment short enough, though, to causes malfunctioning. When REINDEX cancels out due to an error, it does not restore the previous CDX file. As the following sample demonstrates, you end up with an indexless table:

Create Cursor curDemo (cID C(1))
Insert into curDemo Values ("A")
Insert into curDemo Values ("B")
Index on GetID(cID) Tag cID CANDIDATE
? ">", Key(1)
Reindex
? ">", Key(1)
plKill = .T.
Reindex
? ">", Key(1)

Procedure GetID(tcID)
	If Vartype(m.plKill) == "L"
		Return "A"
	Else
		Return m.tcID
	EndIf
EndProc

Run this program and ignore the error message. You can see that KEY(1) returns a valid expression the first two times, but nothing the last time. If you had used a table you would notice that the CDX file is gone. That's only a problem when you encounter an error during the index operation. Aside from problems with memory and network connections, you might encounter errors on CANDIDATE and PRIMARY indexes when you

• added an index without letting check VFP existing data,
• ran into some sort of index corruption that allowed VFP to add multiple records with the same key, or
• have an index on a function that is causing an error.

In any case, it's probably better to avoid these problems and just don't use REINDEX in a production application.