On 2017-11-03 20:21, Frank Cazabon wrote:
Individual indexes on each field should work, not an index on the compound expression.
Yep. That's what I found. Here's a subset of my indexes:
index on city tag city index on state tag state index on deleted() tag mydel BINARY
And then here's my query results from SYS(3054,12,"memvar"):
SELECT o1.invoice, o1.vendor, o1.vendor_id, o1.del_date, o1.equipment, o1.gross, o1.cost, o1.pu_city, o1.pu_st, o1.dest_city, o1.dest_st, o1.qty, o1.product, o1.ven_miles, o1.weight, o1.entered, o1.ven_fha, o1.dot_no, o1.ven_city, o1.ven_state, o1.ven_zip, o1.ven_poc, o1.ven_phone, o1.ven_fax, o1.ven_email, 0 as stop_flag, m.tlAlternate as Alt_flag FROM tmpDetail o1 WHERE pu_city = m.tcPu_City AND pu_st = m.tcPu_St INTO CURSOR Query1 Using index tag Pu_city to rushmore optimize intermediate result Using index tag Pu_st to rushmore optimize intermediate result Using index tag Mydel to rushmore optimize intermediate result Rushmore optimization level for intermediate result: full
Note that without the DELETED() index tag, it would have said Rushmore was PARTIAL.