Hi,
We have a table of accounts and 2 indexes on it.
One uses kco, kledger and kaccount.
The other uses a searchaddress field and is a word index.
I have been told that it isn't possible to create an index with kco, kledger, kaccount and use the word index of searchaddress.
So...
If I do...
for each account where kco = 1
and kledger = "S"
and kaccount = "abc123"
and searchaddres contains "Street*"
no-lock:
Will that combine the 2 indexes? Or is there some other faster way of finding the records I'm after?
I'm editing this as it was originally done with no index fields at all and would sometimes take over half an hour to complete a single search.
Thanks for any help
Oliver
Hi Oliver,
These indexes cannot be combined. However, there is an extremely fast alternative using word-indexes only.
Create a word-indexed dummy field in your table for this purpose. Let's call it 'mywordindex'.
Populate this dummy field for each record as following:
kco1 kledgerS kaccountabc123 addres1200 addressMain addressStreet
So this word-indexed field contains elements from different fields.
Your search now becomes incredibly fast on any combination imaginable as following:
for each account where mywordindex contains "kco1 & kledgerS & kaccountabc123 & addressStreet*"
no-lock:
The best place to populate this index is in a write trigger. If you are against write triggers the challenge is yours.
Thanks Peter,
I though about doing something like this but I wasn't sure how to implement it.
I'll get to work
You say "I'm editing this as it was originally done with no index fields at all and would sometimes take over half an hour to complete a single search.". I can understand that, if you started using no indexes and now have it using indexes, that would make quite a difference in performance, so, the question is, do you still have a performance problem or are you trying to guild a lily? How many records are being returned with the three indexed values?