From below query, a table tdfmstr uses two indexes and conditions added (All conditions are mandatory). After the compilation, I checked XREF of the query. I could see compiler chose 2 indexes opvdor and opsellord.
Could you please tell me is the compiler using both indexes or the 2nd index(opsellord) only? What if I say to compiler only use 1st index by mentioning use-index opvdor in my query.. I understand forcing the compiler is not a good choice but in this case I am not sure how this query will work with larger records and not causing any performance issues.
INDEXES
**opvdor** 3 + tdfcust
+ tdfnum
+ tdfseq
**opsellord** 3 + tdfcust
+ tdfpart
+ tdfvend
QUERY
for each tdf_mstr
where tdfmstr.tdfcust = "SALES"
and tdfmstr.tdfnum = "1"
and tdfmstr.tdfseq = 455
and tdfmstr.tdfpart = "TEST"
and tdfmstr.tdfglob = ""
and tdfmstr.tdfvend = "TOYOTA"
and tdfmstr.tdfeed = "X"
no-lock
use-index opvdor: /*Forcibly given to use 1st Index*/
end.
XREF RESULTS
916 SEARCH edb.tdf opvdor
916 SEARCH edb.tdf opsellord
Using two indexes should be a benefit, not a problem. I don’t understand why you would want to force the query to be less efficient. The WHERE clause that you are showing has equality matches on all of the fields in both indexes which ought to be ideal.
This old thread on Progress Communities gets into how multiple indexes work: https://community-archive.progress.com/forums/00019/61573.html
If you are unsure, run some test cases and track the response time. Or, if you want to get really fancy, use the -zqil startup parameter to report on runtime behaviour. (More info on -zqil: https://community.progress.com/s/article/21216)