String search is very slow with OR statement
Get rid of these silly subqueries and use UNION rather than OR:
select p.* from product p
   join product_stock c on p.id = c.product_id
where c.quantity &gt; 0
  and p.product_number like '%T%'
union
select p.* from product p
   join product_offer o on p.id = o.product_id
where now() between o.offer_start and o.offer_end
  and p.product_number like '%T%';