Index Rule - Confusion

[dhamu14]

Consider Two Unique Indexes for customer table.
Unique Index 1 :" Contact" (Single component Index - Only Contact field)
Unique Index 2: "Phone " (Single component Index - Only phone field)

Query:
for each customer where contact = "test" and phone = "23232323" no-lock:
end.

when I take "Xref" for the above query, the query is picking the "Phone" index. But as per alphabetical order, it should have picked up the Contact index.

Kindly provide suggestions for the above scenario.

 

[TomBascom]

Is "phone" the primary index?

 

[dhamu14]

No, both are not primary Index.
Primary Index is custnum

 

[TomBascom]

The field names are "phone" and "contact" -- what are the index names?

Are you certain that they are both single component unique indexes?

You might want to post the .df file for that table for our consideration.

 

[dhamu14]

They both are Single Component Index. Index Names are also same as field name. I have just created these indexes for my testing purpose. I have attached the df file also

 

[TomBascom]

Interesting. The same thing happens to me.

 

[TheMadDBA]

Also if you add xseq into the where clause it picks that index. Looks like it is going reverse alphabetically for multiple unique indexes.

In theory it shouldn't matter since they are all unique indexes... but it is strange compared to non unique indexes.

 

[andre42]

I recently encountered this issue and asked Progress support about it.
My example was slightly different: I had a unique primary index with four fields and another unique index with the same four fields in a different order.
A find statement queried exactly those four fields with "=". The compiler selected the secondary index, not the primary index.
The first answer that explained something was "I found a statement from Development where they commented that there is a random aspect to index selection when two indexes cannot compared any further because they are equivalent, and this was in a case (unlike yours) where the indexes were not actually the same."
and the final answer was "I have had some feedback from Development, the selection in your case is not quite random. What happens is the list of indexes that are selected is read, and the last index read (alphabetically) is used."

Still somewhat curious, but at least confirmed.