Hi Charlie,

OK, I read the article and, of course *g*, have a number of remarks.

1. When VFP runs a fully optimizable SELECT on one table that results in a filtered table, it doesn't only apply the filter internally. At least, that's how one can understand you text. In fact, VFP actually sets the filter. SET("FILTER") returns the WHERE clause. I don't know how VFP restricts the number of fields, though. AFIELDS() only returns the fields of the result set, DISPLAY STRUCTURE all fields of the underlying table. And it's not SET FIELD that is used here.

2. Your sample about the DOB Select statement is interesting. I verified this with SYS(3054) and VFP is indeed able to combin such expression to one BETWEEN(), even if you try to hide the expression via boolean algebra, eg. not (DOB>Enddate or DOB=Startdate and DOB<=Enddate. I didn't know that and assumed that VFP internally does the opposite, ie translate a BETWEEN() into AND expressions, but obviously it does not. OTOH, it cannot filter out useless filter conditions like repeating the same condition twice. VFP does read the index twice. Of course, this behavior makes sense. :)

3. The size per record in the CDX file. VFP stores the record number in a flexible number of bytes that depend on the total number of records in the table. In fact, VFP combines trail count, duplicate count and record number. The bitmasks are stored in position 14-19 in an exterior (ie leeaf) node, the number of bytes used per pointer is stored at position 23. Additionally, you have the interior nodes that don't contain any record pointers, but pointers to the exterior blocks and the key value that specifies the exterior block.

4. The index key you will find in your example is actually "etersonulsenowskyPeter Pan". As you said, it's backwards. "Peter Pan" is the first record, the next one, "Peter Panowsky" differs only in "nowsky", the third one is identical up to "Peter Pa" and differs in "ulsen" and for the last one the difference is "eterson". The offsets are stored in a compressed format starting right after the number of bytes, ie. byte #24.

Christof

--
Christof