Dyanmic Queries and Indexs

[JamesBowen]

Hi All

I don normally have problems with indexes but I am coming up with one I am not sure how to fix.

I did not realize that you cannot use the "use-index" option on a query prepare string so what is happening is Progress is picking up the wrong index.

PRESELECT EACH Contacts NO-LOCK WHERE Contacts.ID_members EQ 401 AND LOOKUP(Contacts.contact_status,"N,U") NE 0 BY Contacts.first_name BY Contacts.last_name

So my question is what index should I create to match the query above?

The current index I want the query to use goes like this:

idxContactStatus

ID_members
contact_status

​

This is the index that is being used.

idxIDMembers

ID_Members
​

Many Help Thanks.
James.

 

[TomBascom]

Perhaps it is late and I'm suffering a brain cramp but wouldn't:

PRESELECT EACH Contacts NO-LOCK
   WHERE
     Contacts.ID_members EQ 401 AND
     ( contacts.contact EQ "n" or contacts.contact EQ "u" ) 
   BY
     Contacts.first_name BY Contacts.last_name:

Do what you want and result in the proper index being used?

 

[KnutHandsome]

what is happening is Progress is picking up the wrong index.

PRESELECT EACH Contacts NO-LOCK WHERE Contacts.ID_members EQ 401 AND LOOKUP(Contacts.contact_status,"N,U") NE 0 BY Contacts.first_name BY Contacts.last_name

So my question is what index should I create to match the query above?

None. Rewrite the query (perhaps as Tom suggested). The use of LOOKUP in the WHERE clause will force a WHOLE-INDEX search on the primary index (presumably idxIDMembers).

See eg. KB 21098 Single Index Selection Explained

Example 9:

If a function or expression is used for the components of an index, an
index or bracket will not be used.

FOR EACH customer WHERE SUBSTRING(name,1,1) = "A":
Index Used: Custnum

The index on Name will not be used, instead primary index on Custnum
will be used. This expression will result in a full index scan to
retrieve the rows.

 

[JamesBowen]

After doing some more reading a found out the method I was using to return back the index which is being used by a dynamic query was flawed. The method I was using (which I copied from the Progress KBase) only returned back the first index on the current buffer and not the index that is actually being used.

Thanks for everybody's help..