CODEWITHSUNDEEP
mysqlquery-optimization
D555
D555

Reputation: 1832

Mysql query optimisation - Very slow

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.

The query is:

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) >= '2016-06-01' 
        AND DATE(b.booking_time) <= '2016-06-14' 
ORDER BY b.booking_time DESC 
LIMIT 0 , 50

The explain plan shows this:

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

a) How to read this explain plan and know what it means?

b) How can I optimize this query?

Upvotes: 1

Views: 51

Answers (2)

Rick James
Rick James

Reputation: 142208

booking_time is hiding inside a function, so INDEX(booking_time) cannot be used. That leads to a costly table scan.

AND DATE(b.booking_time) >= '2016-06-01' 
AND DATE(b.booking_time) <= '2016-06-14'

-->

AND b.booking_time >= '2016-06-01' 
AND b.booking_time <  '2016-06-15' -- note 3 differences in this line

Or, this might be simpler (by avoiding second date calculation):

AND b.booking_time >= '2016-06-01' 
AND b.booking_time <  '2016-06-01' + INTREVAL 2 WEEK

In the EXPLAIN, I expect the 'ALL' to become 'range', and 'Filesort' to vanish.

Upvotes: 1

Solarflare
Solarflare

Reputation: 11096

To understand the full explain-plan, you should read the documentation, but the most important information it includes is the indexes mysql uses, or, usually more revealing, which it doesn't use.

For your DEPENDENT SUBQUERY (that is your "inline query"), it doesn't use a good index, which makes your query slow, so you need to add the index rej_job_status(booking_id) on your table rej_job_status.

Create it, test it and check your explain plan again, it should then list that new index under key for your DEPENDENT SUBQUERY.

Another optimization might be to add an index rej_booking(booking_time) for your table rej_booking. It depends on your data if it improves the query, but you should try it, since right now, mysql doesn't use an index for that selection.

Upvotes: 0

Related Questions