How to improve/speed up SQL query

I have a query that works, but takes an incredibly long time to run. Is there a more efficient way to accomplish this?

Essentially I want to grab all rows where the location is 106, the purchase_date >= today - 300, the assigned_worker includes Alex or Carol. This produces rows with the same cost_id. I only want one row for each cost_id and would like that row to be the one with the highest cost_num.

select distinct t.cost_id, t.column_a, t.column_b
from mytable t
where  t.location in (106) and t.purchase_date >= today - 300  and (t.assigned_worker like '%Alex%' or d.assigned_worker like '%Carol%' )
and t.cost_num in (select max(cost_num) from mytable where cost_id = t.cost_id
and location in (106));

Solution

It's hard to find the root cause of slowness without knowledge of indexes and explain plan (and - in my personal case - Informix). Few (caution - may be misleading!) ideas:

replace t.location in (106) by t.location = 106
create index on location or purchase_date or cost_id
avoid like '%...' if possible
avoid or - replace by union
replace subselect by window function if Informix supports it (extract rows with row_number() over (partition by location, cost_id order by cost_num desc) equals to 1)
alias table and column in subselect, at least for readability