Index Advise

[Scott24x7]

Hi all,
I was thinking about best practice and Index creation. I remember some big index "Gotcha's" from my Fox 2.6 days, and I'm somewhat skeptical that perhaps, some of them have "gone away" in the later issues.
I'm using VFP9 with all the patches up through and including Sedna.

I noticed in some of my earlier VFP apps that I still have examples of floating around, that for "primary ID" of any major table, I utilized Character data types, but with numeric values. The plus side to this as I recall is, it is then very easy to make compound keys for references into tables like: clientid+contactid (Where these are each 7 digit numbers, and then as an index, they would look like: 10000071000067 (with 1000007 being the client ID and 1000067 being the contact ID).
The upshot to this is, if you just give it the "Client ID" for things like a "DO WHILE" you could make this stupid fast by first doing a SEEK CLIENTID then follow with a DO WHILE CLIENTID.CLIENTID = CONTACT.CLIENTID <blah blah foo foo>.
What was nice about this as I recall is, NO FILTER CONDITIONS.
But... does that matter now in VFP?
I had started using INT values for my primary/candidate key fields. But now I find if I try to do a compound index on that, it doesn't work... Because clientid+contactid now would = 1000074 instead of 10000071000067. On the other hand I can still do a DO WHILE CLIENTID = CONTACT.CLIENTID BUT... now I lose the addition of having them in order. The other thing that I recall being VERY nice about this was I could index on CLIENTID+LASTNAME which would then enable me to keep a "CLIENTID" relationship set with the parent, BUT I could also get the ORDER on the table I wanted without having to use a separate index.
Probably some of you are groaning and thinking what a horrible idea... I'm ok with that, but I'm looking for the "modern technique" than.
My other thought was you convert the INT values at Index time, for something like ALLTRIM(STR(CLIENTID))+ALLTRIM(STR(CONTACTID)) as the compound index. The annoyance there is, you then have to convert all the attempts to seek on those values to STR values as well before you go looking for them. SEEK ALLTRIM(STR(CLIENTID)) for example.
Both seem... messy to me. SO I feel I am probably missing the elegant solution here.
Since my enterprise app is starting to take shape, before it gets too far down the track on one philosophy, I'm very anxious to hear what those of you who have been working in this for yonks now would suggest.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

Griff, I think the maximum length of a path is 260 characters, not 254. But I take your point. I won't say never go beyond, say, 16 characters for a key; but only to do so if you have a good reason. In your case, at least the overall table size is likely to be reasonably small (or maybe not?).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

I right clicked on my Filestore's root directory (All selected) properties tells me I have 942,569 files in 101,102 folder. So maybe not so small. (22.1TB stored on disk).

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[GriffMG]

Mike you are right 260 is the max path.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[GriffMG]

The tables can be large, maybe a couple of million entries in each of two, but they don't live long.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Olaf Doschke]

Joykey,

why would anybody use IDX? There are very seldom use cases for them, even if you'd want a temp index you can create a secondary CDX file and create tags in there.
I'm talking of the max for CDX Tags. And it's a max, of course you keep your index expressions as short as possible. BINTOC() helps, as said it's 4 chars like the integer.

Griff,

From system capacities:
Maximum # of bytes per index key in a compact index: 240
I talk of 120 chars, as any collation aside of MACHINE takes two bytes per char.
I said "for rushmore use MACHINE indexes up to 240 chars".

You normally don't need 240, 120 or even 100 anyway. I d't have bad experiences of indexes making use of the max capacity, though, which in one case was about URLs. your milage may vary of course.

Bye, Olaf.