Optimize where clause query in MySQL

Question

Using alias of a query to join with another table MySQL

Similar to my previous question, it does not work with giving value of 1 from the result of subquery. Therefore I've changed it into

select *
from rents r
where
  r.kickscooter_id in
   (select k.id 
    from support_works sw
    join kickscooters k 
      on k.serial_number = sw.serial_number
    join kickscooter_control_units kcu 
      on  kcu.kickscooter_id =  k.id 
        and kcu.particle_product_id in (9358, 9383)   
    where
      sw.work_type = 'deploy' and
      (sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00'));

but now since it needs to check every r.kickscooter_id, whether it belongs in result list of subquery it is taking too long.

How can I optimize this.

Answer 1

You may try using Join over Sub-query -

select r.*
from rents r
join kickscooters k on k.id = r.kickscooter_id
join support_works sw on k.serial_number = sw.serial_number
join kickscooter_control_units kcu on  kcu.kickscooter_id =  k.id
where kcu.particle_product_id in (9358, 9383)
and sw.work_type = 'deploy'
and sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00'

Later you can create index on below fields to see the differences.

rents(kickscooter_id)
kickscooters(serial_number)
support_works(serial_number, work_type,updated_at)
kickscooter_control_units(kickscooter_id, particle_product_id)