Hi all, I have a problem with indexes in my firebird query.
Below is my query. select a.objid, b.running_qty, b.running_qty2, b.running_totalcost, b.running_lastcost from mm_itrghd a, mm_itrgdt b where (a.objid=b.header_id) and (b.item_id=1200) and (b.wh_id=1) and ((a.postdate<'2010-09-05 00:00:00') or ((a.postdate='2010-09-05 00:00:00') and (a.objid<50000))) order by a.postdate desc, a.objid desc, b.calctyp desc, b.objid desc As you see, in order by section, we use desc. I have an descending index, but my query plan does not use it. It only use index Table A (a.objid) and Table B (b.item_id, b.wh_id) Is there something i missed? What index do you think should i create? Index for Table A (mm_itrghd) (TR_CODE, DOC_ID) Ascending (OBJID) Ascending (TR_CODE) Ascending (POSTDATE) Ascending (POSTDATE, OBJID) Ascending (POSTDATE, OBJID) Descending Index for Table B (mm_itrgdt) (HEADER_ID) Ascending (ITEM_ID) Ascending (WH_ID) Ascending (LOT_NO) Ascending (SERIAL_NO, ITEM_ID) Ascending (HEADER_ID, ITEM_ID, WH_ID, SERIAL_NO, LOT_NO) Ascending (HEADER_ID, ITEM_ID, WH_ID) Ascending (CALCTYP, OBJID) Ascending (ITEM_ID, WH_ID) Ascending (CALCTYP, OBJID, ITEM_ID, WH_ID) Ascending (CALCTYP, OBJID) Descending (OBJID, ITEM_ID, WH_ID) Descending (OBJID) Descending Thanks in advance Regards, Reynaldi
