Too long time taken by Mysql function -

i have table named 'item' approximately 8500 rows , table named 'stock' stores stock of items date-wise , location-wise (i have 2 locations of stock).

the stock table structure given below:

stock_dt date           - stores date of stock, loc_id varchar(3)       - stores location of stock, item_id varchar(6)      - stores item code, item_qty decimal(20,2)  - stores item stock quantity, item_cost decimal(20,2) - stores item stock cost value 

whenever there movement of item from-or-to stores, row inserted stock table updated stock figures specific item. stock table contains 125,000 rows.

now have 1 function takes 2 date parameters (adt_from , adt_to), , retrieves stock of each item @ each location on both dates i.e. opening , closing stock of items @ each location , other computations stocks...

below section of function taking unusually long time (almost 15 minutes).

" -- variable declarations

declare cur_item cursor select item_id   item order item_id;  declare cur_loc cursor select loc_id   location;  declare continue handler not found set not_found = true;  set not_found = false;  open cur_item;  fetch cur_item s_item;  read_item: loop     if not_found         leave read_item;     end if;      set d_opng_qty = 0;     set d_opng_cost = 0;     set d_cls_qty = 0;     set d_cls_cost = 0;      open cur_loc;      fetch cur_loc s_loc;      read_loc:     loop         if not_found             leave read_loc;         end if;          -- opening stock:         select item_qty,                item_cost           d_opng_qty,                d_opng_cost           stock          stock_dt < adt_from         ,    loc_id = s_loc         ,    item_id = s_item         order stock_dt desc         limit 1;          -- closing stock:         select item_qty,                item_cost           d_cls_qty,                d_cls_cost           stock          stock_dt <= adt_to         ,    loc_id = s_loc         ,    item_id = s_item         order stock_dt desc         limit 1;          fetch cur_loc s_loc;     end loop;      fetch cur_item s_item;       end loop; 

"

stock table indexed on stock_dt, loc_id , item_id.

i have tried enabling query_cache , set query_cache_size 128m there seems no improvement @ all.

i commented 2 queries on stock , program finishes rest of execution in few seconds. suggestion?

i guess here problem caused order by.

assuming have composite index on (item, location, date), think need

order item desc, location desc, date desc 

in order search start @ correct end , return after 1 hit.

the reason though hitting composite index (item, location, date) 1 item, traverse index in direction of significant column (implicit order item asc, location asc). hence need scan whole of item's partial history obtain recent record.

i try commenting out order , see if causing problem.