S
smat-consulting
Guest
It doesn't surprise me the FIND is doing a whole-Index scan. It does surprise me that FOR EACH does not. 
I never use OR in the where clause (except maybe with a temp-table and then only when I am 100% sure that Hell would freeze over before there would be more than a couple of records in the temp-table). I rather do two separate statements. FIND FIRST WHERE (case a). IF NOT AVAILABLE FIND FIRST (case b). Similar with FOR EACH - extract the body of the loop and do two loops... I'd be curious to see whether that index would be used, if you extract the "wft_activity_log.process_log_obj = 1" and just have the "status_key part" within the OR. I forgot the exact rule, but it could be that it would just ignore the OR part of the WHERE but use the first part... WHERE wft_activity_log.process_log_obj = 1 AND ( wft_activity_log.status_key = "Active" OR wft_activity_log.status_key = "Busy" )
Continue reading...
I never use OR in the where clause (except maybe with a temp-table and then only when I am 100% sure that Hell would freeze over before there would be more than a couple of records in the temp-table). I rather do two separate statements. FIND FIRST WHERE (case a). IF NOT AVAILABLE FIND FIRST (case b). Similar with FOR EACH - extract the body of the loop and do two loops... I'd be curious to see whether that index would be used, if you extract the "wft_activity_log.process_log_obj = 1" and just have the "status_key part" within the OR. I forgot the exact rule, but it could be that it would just ignore the OR part of the WHERE but use the first part... WHERE wft_activity_log.process_log_obj = 1 AND ( wft_activity_log.status_key = "Active" OR wft_activity_log.status_key = "Busy" )Continue reading...