Justin,
I think the proper index would be STR(InvoiceID,LengthOfInteger) + STR(LineID,LengthOfInteger)--never use ALLTRIM in an index expression, and especially not in combination with another STR() function.
With this tag we could
SELECT * FROM Items I WHERE STR(I.InvoiceID,x) + STR(I.LineID,x) = STR(m.InvoiceID,x) and get all the lines for a specific invoice.
I think either way--creating the compound expression, or using the individual tags would work very well in your case because either way there is high selectivity in the indices. The place where it might be an issue is where the table is large, and there's a EndDate > SomeDate and that expression ends up returning half the pointers in a table.
I haven't tested Integer vs. Character ID fields, but am aware of the articles by Malcolm Rubel stating Integers are faster. I wonder if the diffence isn't largely due to making the expresion as small as possible, ie. with integer, 4 bytes for 4 billion possibilities. IOW, if you're going to having millions of invoices and choose a character field, how wide doesn't it have to be?
Charlie


>I have been following this thread and have a question to ask concerning SEEK versus LOCATE, but first I need to paint the picture for all to see. It is my understanding that in order to maximize the speed of an application's data access that a programmer is advised to use integer fields. However, when you have to tables, such as invoice header and invoice line items, the invoice line items table will have two integer fields -- lineid (which is not the same as linenumber, but rather is the primary key for the table) and invoiceid (which is, obviously, used to relate the line item to the invoice header table.) Suppose you would like to seek a using lineid and invoiceid to a particular record. You would have to create an index on ALLTRIM(STR(lineid)) + ALLTRIM(STR(invoiceid)) in order for the seek to work. This essentially eliminiates (according to my interpretation of VFP Adivisor) the advantage of using integers.
>
>Now, my question then becomes, is it more advisable to use character fields for the primary key with compound indexes and seek to find specific records, or are you better to use integer fields with individual indexes combinded with a 'LOCATE FOR this = that AND here = there'?