Hello Experts Exchange
I have a MySQL query that takes a long time to run and is not returning any data, but parts of the query run quickly when I run smaller sections.
This is my query that returns no data.
Select Z.zip,Sum(sl_order.sales_qty) as sales_qty
inner join sl_order on sl_transaction.id = sl_order.transaction_id
inner join (select dl2.id,stk_transfer.received_date,stk_transfer.stk_item_id, address.zip
inner join data_location dl2 on stk_transfer.del_loc_id = dl2.id
inner join address on dl2.address_id = address.id
Where stk_transfer.received_date between DATE_FORMAT('2016-05-01','%Y-%m-%d %h:%i:%s')
and DATE_FORMAT('2016-08-26','%Y-%m-%d %h:%i:%s')
and desp_loc_id = 101
and del_loc_id <> 8) as Z
on Z.id = sl_transaction.location_id
and Z.received_date < sl_transaction.updated_at
and Z.stk_item_id = sl_order.stk_item_id
Where sl_transaction.updated_at > DATE_FORMAT('2016-05-01','%Y-%m-%d %h:%i:%s')
group by Z.zip
I think it is performing a full table scan of the sl_order table but I don't know how to change my query to stop that.
Can anyone suggested how I can change my query so it returns data?