Mysql query optimisation - Very slow
<p>I need to optimise my query which is running very slow, but don't know how to do it. It contains a subquery which is making it very slow. If I remove the inline query then it runs very well.</p>

<p>The query is:</p>

<pre><code>EXPLAIN
SELECT  t.service_date, 
        t.service_time, 
        (SELECT js.modified_date FROM rej_job_status js WHERE js.booking_id=b.booking_id ORDER BY id DESC LIMIT 1) `cancel_datetime`, 
        b.booking_id, 
        b.ref_booking_id, 
        b.phone, b.city, 
        b.booking_time, 
        CONCAT(rc.firstname," ",rc.lastname) customer_name, 
        rc.phone_no, 
        rs.service_id,
        rs.service_name,
        rct.city_name 
FROM rej_job_details t 
JOIN rej_booking b ON t.booking_id = b.booking_id 
JOIN rej_customer rc ON rc.customer_id = b.customer 
JOIN rej_service rs ON t.service_id = rs.service_id 
JOIN rej_city rct ON rct.city_id=b.city 
WHERE t.act_status = 0 AND DATE(b.booking_time) &gt;= '2016-06-01' 
        AND DATE(b.booking_time) &lt;= '2016-06-14' 
ORDER BY b.booking_time DESC 
LIMIT 0 , 50 
</code></pre>

<p>The explain plan shows this:</p>

<pre><code>id  select_type         table   type    possible_keys       key         key_len ref                         rows    Extra   
1   PRIMARY             b       ALL     PRIMARY             NULL        NULL    NULL                        32357   Using where; Using filesort
1   PRIMARY             rct     eq_ref  PRIMARY             PRIMARY     4       crmdb.b.city        1       NULL
1   PRIMARY             t       ref     booking_id          booking_id  4       crmdb.b.booking_id  1       Using where
1   PRIMARY             rs      eq_ref  PRIMARY,service_id  PRIMARY     4       crmdb.t.service_id  1       NULL
1   PRIMARY             rc      eq_ref  PRIMARY             PRIMARY     4       crmdb.b.customer    1       Using where 
2   DEPENDENT SUBQUERY  js      index   NULL                PRIMARY     4       NULL                        1       Using where
</code></pre>

<p>a) How to read this explain plan and know what it means?</p>

<p>b) How can I optimize this query?</p>